SQL query to join multiple tables

[Digital Parasite]

Thanks Bok, that works! You are da man!

That would have taken me a long time to figure out and I was already banging my head hard enough. I was thinking all about joins instead of unions.

Jeff.

[Digital Parasite]

Bok and all you other SQL experts out there. I'm now trying to take a MySQL table and try to find all the records where a bID matches the criteria for cID = 1 *AND* CID = 2. I have figured out a way to do it but it seems quite complicated. Is there an easier way to get these same results?

create table testAND (aID int auto_increment primary key, bID int, cID int, val_timestamp timestamp,val_int int, val_float float);
insert into testAND values (NULL,1,1,'20091214002233',NULL,1.05);
insert into testAND values (NULL,1,2,'20091214002233',105,NULL);
insert into testAND values (NULL,2,1,'20091214002233',NULL,1.95);
insert into testAND values (NULL,2,2,'20091214002233',115,NULL);
insert into testAND values (NULL,3,1,'20091214002233',NULL,1.15);
insert into testAND values (NULL,3,2,'20091214002233',130,NULL);
insert into testAND values (NULL,1,1,'20091214002244',NULL,0.50);
insert into testAND values (NULL,1,2,'20091214002244',150,NULL);
insert into testAND values (NULL,2,1,'20091214002244',NULL,1.25);
insert into testAND values (NULL,2,2,'20091214002244',125,NULL);
insert into testAND values (NULL,2,2,'20091214002255',25,NULL);
insert into testAND values (NULL,2,1,'20091214002255',NULL,2.55);

If you create the table above, I'm trying to find all (cID = 1 AND (val_float >= 1.00 AND val_float <= 2.00) AND (cID = 2 AND (val_int < 120 OR val_int > 140). The way I figured to do this was use INNER JOIN to be able to match two different attributes at the same time but then I want to display the results of both sets of attributes so I do it twice, then use UNION. Here is my SQL statement:

SELECT t1.bID, t1.cID, t1.val_timestamp, t1.val_int, t1.val_float FROM testand t1 
INNER JOIN testand t2 ON t1.bID = t2.bID 
WHERE (t1.cID = 2 AND (t1.val_int < 120 OR t1.val_int > 140)) 
AND (t2.cID = 1 AND (t2.val_float >= 1.00 AND t2.val_float <= 2.00)) 
GROUP BY t1.val_timestamp, t1.val_int
UNION
SELECT t2.bID, t2.cID, t2.val_timestamp, t2.val_int, t2.val_float FROM testand t1 
INNER JOIN testand t2 ON t1.bID = t2.bID 
WHERE (t1.cID = 2 AND (t1.val_int < 120 OR t1.val_int > 140)) 
AND (t2.cID = 1 AND (t2.val_float >= 1.00 AND t2.val_float <= 2.00)) 
GROUP BY t2.val_timestamp, t2.val_float 
ORDER BY bID, cID;

This correctly (as far as I can tell) produces these results:

+------+------+---------------------+---------+-----------+
| bID  | cID  | val_timestamp       | val_int | val_float |
+------+------+---------------------+---------+-----------+
|    1 |    1 | 2009-12-14 00:22:33 |    NULL |      1.05 |
|    1 |    2 | 2009-12-14 00:22:33 |     105 |      NULL |
|    1 |    2 | 2009-12-14 00:22:44 |     150 |      NULL |
|    2 |    1 | 2009-12-14 00:22:33 |    NULL |      1.95 |
|    2 |    1 | 2009-12-14 00:22:44 |    NULL |      1.25 |
|    2 |    2 | 2009-12-14 00:22:33 |     115 |      NULL |
|    2 |    2 | 2009-12-14 00:22:55 |      25 |      NULL |
+------+------+---------------------+---------+-----------+
7 rows in set (0.02 sec)

Is there a faster/shorter/more efficient way to get the same results? I have a feeling I'm missing something obvious.

Thanks.

[Bok]

Try the explain query on it, it's not efficient.

Perhaps I'm not quite understanding what you are trying to do and I don't know why you are doing group by at all.

Wouldn't this give you the correct results

SELECT * FROM testAND WHERE (cID = 2 AND (val_int between 120 and 140)) OR (cID = 1 AND (val_float between 1.00 and 2.00)) order by cID;

?

[Digital Parasite]

Try the explain query on it, it's not efficient.

Perhaps I'm not quite understanding what you are trying to do and I don't know why you are doing group by at all.

Wouldn't this give you the correct results

SELECT * FROM testAND WHERE (cID = 2 AND (val_int between 120 and 140)) OR (cID = 1 AND (val_float between 1.00 and 2.00)) order by cID;

That does not give the correct results. First, I'm not trying to find values between 120 and 140, but less than 120 and greater than 140, so everything except between 120 and 140.

But if you re-write the query to be:
SELECT * FROM testAND WHERE (cID = 2 AND (val_int < 120 OR val_int > 140)) OR (cID = 1 AND (val_float between 1.00 and 2.00)) order by bID, cID;

That still won't give you the correct results. Because you have OR between the two statements. I only want to display the results if both are true.

So that query returns:

+-----+------+------+---------------------+---------+-----------+
| aID | bID  | cID  | val_timestamp       | val_int | val_float |
+-----+------+------+---------------------+---------+-----------+
|   1 |    1 |    1 | 2009-12-14 00:22:33 |    NULL |      1.05 |
|   2 |    1 |    2 | 2009-12-14 00:22:33 |     105 |      NULL |
|   8 |    1 |    2 | 2009-12-14 00:22:44 |     150 |      NULL |
|   3 |    2 |    1 | 2009-12-14 00:22:33 |    NULL |      1.95 |
|   9 |    2 |    1 | 2009-12-14 00:22:44 |    NULL |      1.25 |
|   4 |    2 |    2 | 2009-12-14 00:22:33 |     115 |      NULL |
|  11 |    2 |    2 | 2009-12-14 00:22:55 |      25 |      NULL |
|   5 |    3 |    1 | 2009-12-14 00:22:33 |    NULL |      1.15 |
+-----+------+------+---------------------+---------+-----------+

The last row, bID=3 should not be there, while 1.15 is between 1.00 and 2.00 the cID=2 val_int is 130 which is not in the valid range. Since I'm looking for results only when both cID=1 AND cID=2 are true, this is returning too much information. I need to remove the cases where they are not both true.

Does that explain better?

The reason why I was using group by in my statement was to get rid of the multiple rows in the join statement. For every val_int it prints multiple val_floats, so using GROUP BY I was trying to reduce it to a single val_int or val_float for each row.

[Bok]

That does not give the correct results. First, I'm not trying to find values between 120 and 140, but less than 120 and greater than 140, so everything except between 120 and 140.

my bad on that one, misread it.

The last row, bID=3 should not be there, while 1.15 is between 1.00 and 2.00 the cID=2 val_int is 130 which is not in the valid range. Since I'm looking for results only when both cID=1 AND cID=2 are true, this is returning too much information. I need to remove the cases where they are not both true.

How can cID = 1 and 2 in the same row?

Does that explain better?

Nope, more confused than ever, perhaps it's the dang heat here >90F right now..!!

The reason why I was using group by in my statement was to get rid of the multiple rows in the join statement. For every val_int it prints multiple val_floats, so using GROUP BY I was trying to reduce it to a single val_int or val_float for each row.

[Digital Parasite]

my bad on that one, misread it.

How can cID = 1 and 2 in the same row?

Nope, more confused than ever, perhaps it's the dang heat here >90F right now..!!

cID can't be 1 and 2 in the same row. Let me convert the problem to english words and simplify it to remove the ranges.

bID = Patient
cID is an Attribute
cID = 1 for example means temperature
cID = 2 for example means heart rate

What I'm trying to find is: show all Patients that have a temperature of 1.95 AND a heart rate of 150, and list those temperatures and heart rates with timestamps.

So Temperature and Heart Rate are not listed on the same row which is what makes this tricky, I need to see if they match the pattern I'm looking for and then only display the ones that match the pattern. In my example Patient #3 (bID = 3) has a temperature that matches the pattern but his heart rate does not so I don't want to list him or the details.

Does that clarify anything? Wow, big heat, we were up to 85F on the weekend which is very out of the ordinary for the beginning of April way up north here.

[gopher_yarrowzoo]

That does make it rather tricky to say the least..
I take it the table is in the from ID,cID,Data,.... and is such that ID,1,Temp then next row is ID,2,Heartrate ?
See if it is that I'd be tempted to create a temp table / array
thus
table / array [id][timestamp] [heartrate] ,[id][timestamp] [temp], [id][timestamp] [other]
table ID, Timestamp, Temp, Heartrate, other data .....
filled as if ID, timestamp, cID==1 fill Temp || cID==2 fill Heartrate, other data

query is the something like SELECT * from temp_table WHERE !(Temp>=120 && Temp<=140) && Heartrate is in range.
then the array is simple.
-TABLE-
ID | timestamp | Temp | Heart Rate | Other Data
run the array and display it 1 per row.