MySQL puzzle; combine queries using JOIN or other method? 1

[QEB]

Hi! I'm fairly new to the more challenging MySQL queries. I'm currently wrestling with something that probably justifies a JOIN of some sort. Here's two SELECT statements that I want to combine;

1-
SELECT table1.col1 aa, table1.col2 ab, table1.col3 ac, table1.col4 ad, table1.col5 ae, table1.col6 af, table2.col1 ba, table2.col2 bb, table2.col3 bc FROM db.table1, db.table2 WHERE table1.col1 = "a certain number" AND table2.col1 = "some value" AND table1.col2 = table2.col2 AND table1.col4 = table2.col3 ORDER BY table1.col2 ASC

2-
SELECT table1.col1 aa, table1.col2 ab, table1.col3 ac, table1.col4 ad, table1.col5 ae, table1.col6 af, table2.col1 ba, table2.col2 bb, table2.col3 bc FROM db.table1, db.table2 WHERE table1.col1 = "a certain number" AND table2.col1 = "the oposite value from SELECT#1" AND table1.col2 = table2.col2 AND table1.col4 = table2.col3 ORDER BY table1.col2 ASC

When I combine the two like this:
3-
SELECT table1.col1 aa, table1.col2 ab, table1.col3 ac, table1.col4 ad, table1.col5 ae, table1.col6 af, table2.col1 ba, table2.col2 bb, table2.col3 bc FROM db.table1, db.table2 WHERE table1.col1 = "a certain number" AND (table2.col1 = "some value" OR table2.col1 = "the oposite value from SELECT#1&quot AND table1.col2 = table2.col2 AND table1.col4 = table2.col3 ORDER BY table1.col2 ASC
all values I want are shown, though on separate rows. I want to calculate percentages with the returned results. I'll explain.

Using query 3, I get:
aa | ab | ac | ad | af | ba | bb | bc
3830 | 2004-01-26 15:26:06 | 137 | 101 | 1169 | 137 | 101 | pin-mcr ok
3830 | 2004-01-26 15:26:06 | 140 | 101 | 264 | 140 | 101 | pin-mcr nok
3830 | 2004-01-26 15:29:42 | 137 | 101 | 1176 | 137 | 101 | pin-mcr ok
3830 | 2004-01-26 15:29:42 | 140 | 101 | 266 | 140 | 101 | pin-mcr nok
3830 | 2004-01-28 13:13:59 | 137 | 101 | 1192 | 137 | 101 | pin-mcr ok
3830 | 2004-01-28 13:13:59 | 140 | 101 | 282 | 140 | 101 | pin-mcr nok
3830 | 2004-01-28 13:46:08 | 137 | 101 | 1192 | 137 | 101 | pin-mcr ok
3830 | 2004-01-28 13:46:08 | 140 | 101 | 282 | 140 | 101 | pin-mcr nok

where column "af" is the counter, column "bc" a description. Now, to get the total for rows 1 & 2, which are related (pin-mcr ok & pin-mcr nok in column "bc&quot I need to add values "af" of both first 2 rows. pin-mcr ok + pin-mcr nok = 100%. When I have this, I can calculate the percentages pin-mcr ok & pin-mcr nok. So, is there a way to combine both query 1 & query 2, to get the entire query to show row 1 & 2 in one row, 3 & 4 in one row, 5 & 6 in one row, etcetera ...

I tried playing with left, outer & inner JOINs. I also scrutinized the MySQL forum. I'm working with a MySQL server version 3.23.58, so I can't have any obvious nor obscure UNIONs (hence the JOINs). Does anyone have a pointer in a possibly correct direction?

Very best regards,
- Alex.

 

[r937]

how do you know which rows belong together? it appears to be aa and ab

this is obviously going to involve a GROUP BY

rudy

http://r937.com/

SQL Consulting

 

[QEB]

Wow, is this fast! I'm impressed. The GROUP BY I left out for now, as I thought things would become to complicated, it could be the date though, column ab, for these are the same (in pairs). These two rows are a pair:
3830 | 2004-01-26 15:26:06 | 137 | 101 | 1169 | 137 | 101 | pin-mcr ok
3830 | 2004-01-26 15:26:06 | 140 | 101 | 264 | 140 | 101 | pin-mcr nok
and so on ...
I visited your site & ended up subscribing to searchdatabase-dot-com. There I found an interesting one; Calculations between two rows in one table (posed on january 12 2004). This looks like a direction to look at. However, I played with JOINS in vain so far ...

 

[r937]

it could be the date?

okay

[tt]select table1.col2 ab
from db.table1
inner
join db.table2
on table1.col2 = table2.col2
and table1.col4 = table2.col3
where table1.col1 = number
and table2.col1
in ( 'some value'
, 'the opposite value' )
group
by table1.col2
order
by table1.col2 [/tt]

rudy

http://r937.com/

SQL Consulting

 

[QEB]

This looks like almost bingo, I'm working with it! However, the
"join db.table2
on table1.col2 = table2.col2"
statement assumes that there's a date in both tables. The date only resides in table1. Table2 has no date.

I'm in need of this
"table1.col3 = table2.col1 AND table1.col4 = table2.col2"

in order to combine both tables. As table2 contains descriptions of actions consisting of two separate numbers in both tables. Both reflected by columns "ac", "ad" and columns "ba" and "bb" (above). If I group my "original" query 3 on the date, it only shows the OK or NOK result. Not both ...

 

[r937]

i am, like, totally confused

first, you name your tables with the helpful col1, col2, etc.

then you alias these to aa, ab, etc,

then you say that ab is the date, and that you want to group by the date

so i trace that back to col2 and find that you have

table1.col2 = table2.col2

in your original queries

and now you are saying table2 has no date?

well, you have the general idea from the GROUP BY query i gave you, so just change the names as necessary

good luck



rudy

http://r937.com/

SQL Consulting

 

[QEB]

I'm probably looking at it with too much haste, as always things have to be done & dealt with by yesterday. Perhaps I made a big cow'ish typo. OK, in words: Two tables, one with occurances, the second with a description of all occurances. I look up the description of occurances in table2 by chacking a number consisting of two fields. These two fields are present in both tables. The first table has a date/time field. For every date/time combination there's an OK-occurance (all went well) & a NOK-occurance (all went wrong). I can't seem to have both the OK & NOK result of one date/time on one row. It's an alternating table instead. I want to do a percentage calculation regarding these OK & NOK occurances. How much went right, & how much went wrong. As there are 61 types of occurances (& growing) I need to check, I'd like to keep the solution generic, instead of solving things by scripting (PHP or ASP). Besides that, scripting is the most time-absorbing solution. Maybe my haste is indeed a culprit in this. I somehow feel I'm overlooking a very clear issue here ... whenever I stumble across a resolution I'll post it here. Ofcourse, your time & efforts are hugely appreciated! Very much so indeed.

 

[QEB]

OK, I'll take a wee bit more time, I thought. Well, I didn't have to, the dark room was suddenly lit. I went wrong by using about 2/3 of a JOIN, ofcourse I need an entire JOIN. I merely had to alias both tables used in order to get a correct recordset. Aaaaaarrghh ... I just aliased one which resulted in an incorrect recordset (DUH).

here it is, the thing that works, nothing too fancy either;

SELECT a1.col1 identifier, a1.col2 the_date_it_all_happened, to_days(a1.col2) just_the_date_without_time, MAX(b1.col3) all_went-OK, MAX(b2.col3) all_went_wrong
FROM table1 a1, aliasedtable1 a2, table2 b1, aliasedtable2 b2
WHERE a1.col2 = b1.col2 and a1.col4 = b1.col4 and a1.col1 = "some_number" and a2.col2 = a1.col2 and b1.col5 = "all_went_well" and b2.col5 = "all_went_terribly_wrong" and a2.col1 = "som_number" and ...(basically the same where clause but on both the aliased tables) ... GROUP BY just_the_date_without_time ORDER BY the_date_it_all_happened DESC

My mistake for looking into the wrong directions. Your inner joins pointed me towards right where I wanted to be. My gaze was fogged up panicing people around me I guess. Thanks r937! Much, very much appreciated!

 

[r937]

glad i could help



rudy

http://r937.com/

SQL Consulting