SQL inner join from field defined table?
- by Wolftousen
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.
