SQL query to join multiple tables

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