SQL inner join from field defined table?

Posted by Wolftousen on Stack Overflow See other posts from Stack Overflow or by Wolftousen
Published on 2010-04-14T22:57:02Z Indexed on 2010/04/14 23:03 UTC
Read the original article Hit count: 116

Filed under:

I have a, currently, a total of 6 tables that are part of this question. The primary table, tableA, contains columns that all the entries in the other 5 tables have in common. The other 5 tables have columns which define the entry in tableA in more detail.

For example:
TableA
ID|Name|Volumn|Weight|Description
0 |T1 |0.4 |0.1 |Random text
1 |R1 |5.3 |25 |Random text

TableB
ID|Color|Shape
0 |Blue |Sphere

TableC
ID|Direction|Velocity
1 |North |3.4

(column names are just examples don't take them for what they mean...)

The ID field in Table A is unique to all other tables (i.e. TableB will have 0, but TableC will not, nor any other Tables).

What I would like to do is select all the fields from TableA and the corresponding (according to ID field) detail Table (TableB-F).

What I have currently done and not tested is added a field to TableA so it looks like this:
TableA
ID|Name|Volumn|Weight|Description|Table
0 |T1 |0.4 |0.1 |Random text|TableB
1 |R1 |5.3 |25 |Random text|TableC

I have a few questions about this:

1.Is it proper to do such a thing to TableA, as foreign keys wont work in this situation since they all need to link to different tables?

2.If this is proper, would the SQL query look like this (ID would be input by the user)?
SELECT * FROM TableA AS a INNER JOIN a.Table AS t ON a.ID = ID;

3.Is there a better way to do this?

Thanks for the help.

© Stack Overflow or respective owner

Related posts about sql