SQL query to join multiple tables

[Bok]

best guess though..

mysql> create table y1 (aID int auto_increment primary key, bID int, cID int, val_timestamp timestamp,val_char varchar(255));
Query OK, 0 rows affected (0.06 sec)

mysql> create table y2 (aID int auto_increment primary key, bID int, cID int, val_timestamp timestamp,val_float float);
Query OK, 0 rows affected (0.01 sec)

mysql> create table y3 (aID int auto_increment primary key, bID int, cID int, val_timestamp timestamp,val_int int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into y1 values (1,1,1,curdate(),'qqqq');
Query OK, 1 row affected (0.00 sec)

mysql> insert into y2 values (1,1,1,curdate(),5.5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into y3 values (1,1,1,curdate(),9);
Query OK, 1 row affected (0.01 sec)

mysql> select a.cID,a.val_timestamp,a.val_char,b.val_float,c.val_int from y1 a join y2 b on a.bID = b.bID join y3 c on a.bID = c.bID where a.bID = 1;
+------+---------------------+----------+-----------+---------+
| cID  | val_timestamp       | val_char | val_float | val_int |
+------+---------------------+----------+-----------+---------+
|    1 | 2009-12-14 00:00:00 | qqqq     |       5.5 |       9 |
+------+---------------------+----------+-----------+---------+
1 row in set (0.00 sec)

Does that help ?

[Digital Parasite]

best guess though..

Does that help ?

So your table creation is bang on, that is essentially what is looks like. Your SQL code executes but the problem is that you are showing a.cID,a.val_timestamp,a.val_char,b.val_float,c.val_int but not b.cID or c.cID, b.val_timestamp, or c.val_timestamp.

All of those are independent. In your example you had 1 entry in each table which works because they all have the same time stamp and cID.

But if you did then this on top of what you already inserted:

insert into y1 values (NULL,1,2,'20091214001122','xxx');
insert into y2 values (NULL,1,7,'20091214002233',1.95);
insert into y3 values (NULL,1,4,'20091214003344',42);

And then ran your select statement, you would just see y1's cID of '1' and '2' and y1's timestamps, but not the other timestamps, and each value is repeated several times as seen here:

+------+---------------------+----------+-----------+---------+
| cID  | val_timestamp       | val_char | val_float | val_int |
+------+---------------------+----------+-----------+---------+
|    1 | 2009-12-14 00:00:00 | qqqq     |       5.5 |       9 |
|    2 | 2009-12-14 00:11:22 | xxx      |       5.5 |       9 |
|    1 | 2009-12-14 00:00:00 | qqqq     |      1.95 |       9 |
|    2 | 2009-12-14 00:11:22 | xxx      |      1.95 |       9 |
|    1 | 2009-12-14 00:00:00 | qqqq     |       5.5 |      42 |
|    2 | 2009-12-14 00:11:22 | xxx      |       5.5 |      42 |
|    1 | 2009-12-14 00:00:00 | qqqq     |      1.95 |      42 |
|    2 | 2009-12-14 00:11:22 | xxx      |      1.95 |      42 |
+------+---------------------+----------+-----------+---------+
8 rows in set (0.00 sec)

Ideally I would like to see something like this (where bID = 1):

+------+---------------------+----------+-----------+---------+
| cID  | val_timestamp       | val_char | val_float | val_int |
+------+---------------------+----------+-----------+---------+
|    1 | 2009-12-14 00:00:00 | qqqq     |    (NULL) |  (NULL) |
|    1 | 2009-12-14 00:00:00 | (NULL)   |       5.5 |  (NULL) |
|    1 | 2009-12-14 00:00:00 | (NULL)   |    (NULL) |       9 |
|    2 | 2009-12-14 00:11:22 | xxx      |    (NULL) |  (NULL) |
|    7 | 2009-12-14 00:22:33 | (NULL)   |      1.95 |  (NULL) |
|    4 | 2009-12-14 00:33:44 | (NULL)   |    (NULL) |      42 |
+------+---------------------+----------+-----------+---------+

Is there any way to get that to happen? Or if not, can I do 3 different selects one for each table and have the results populated in one result set to work with all the variables?

Thanks.
Jeff.

[Bok]

perhaps, with a little fudging

mysql> select cID,val_timestamp,val_char,NULL as val_float,NULL as val_int from y1 where bID = 1 union  select cID,val_timestamp,NULL,val_float,NULL from y2 where bID = 1 union select  cID,val_timestamp,NULL,NULL,val_int from y3 where bID = 1 order by cID;
+------+---------------------+----------+-----------+---------+
| cID  | val_timestamp       | val_char | val_float | val_int |
+------+---------------------+----------+-----------+---------+
|    1 | 2009-12-14 00:00:00 | qqqq     |      NULL |    NULL |
|    1 | 2009-12-14 00:00:00 | NULL     |       5.5 |    NULL |
|    1 | 2009-12-14 00:00:00 | NULL     |      NULL |       9 |
|    2 | 2009-12-14 00:11:22 | xxx      |      NULL |    NULL |
|    4 | 2009-12-14 00:33:44 | NULL     |      NULL |      42 |
|    7 | 2009-12-14 00:22:33 | NULL     |      1.95 |    NULL |
+------+---------------------+----------+-----------+---------+
6 rows in set (0.00 sec)

[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.