SQL query to join multiple tables

[Digital Parasite]

I must be searching using the wrong key words but how does one join multiple tables with SQL that share the same data type so you get them in the same column?

To better explain let me give you an example. I have 3 tables:
data_char, data_float, data_integer

They all have the first 4 columns in common, but the 5th column is different. So they all have aID (int), bID (int), cID (int), val_timestamp (timestamp)

The data_char has a val_char (varchar 255), the data_float has a val_float (float), and the data_integer has a val_int (int) column.

The aID's in each table are auto-incrementing values and not related to each other, but the bID is how I link all the tables together.

Is there a way to write a query (I'm using MySQL) to return a result set that has:

cID  val_timestamp   val_char  val_float  val_int

for a specific bID?

I can't find a way to get the cID and val_timestamp columns from each table to merge into a single column, they are coming out as cID cID_1 cID_2 sort of thing.

Or what would you recommend to pull out that data from different tables?

Thanks,
Jeff.

[gopher_yarrowzoo]

there should be jeff let me think.... my SQL is a bit rusty.

SELECT a.bID , a.cID, a.val_timestamp, a.data_char (name of col), b.bID , b.cID, b.val_timestamp,b.data_float, c.bID , c.cID, c.val_timestamp, c.data_int AS a=data_char, b=data_float, c=data_int WHERE a.bID == b.bID && a.bID==c.bID
^^^ should work else you need to use the JOIN command, maybe Bok can help ya out a bit more since he works with it more often than me.

[Digital Parasite]

I can't seem to get that to work, the syntax is not right and I'm not sure how to fix it. Isn't the AS just for creating aliases so you can refer to something by another name but will it actually put data into the same column?

Bok, any ideas?

Thanks.

[Bok]

can you post the table creation details ?

i.e. do

show create table <tablename> for each one.

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