SQL inner join from field defined table?

Question

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|Volume|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|Volume|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 won't 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.

Answer 1

1. If you say that TableA is a primary table, then all detail tables(TableB,TableC,etc) should have FK on A, not vice versa.

2. To select a record from TableA and all details from other tables use LEFT JOIN:

   SELECT A.*, B.color,B.shape, C.direction, C.velocity
FROM TableA A
LEFT JOIN TableB B ON (B.id = A.id)
LEFT JOIN TableC B ON (C.id = A.id)

Answer 2

This is a common way of dealing with a super-type/sub-type model. For instance, Employee and Customer are sub-types of Person...

PERSON

Id|PType|Name      
--+-----+----------
 1|EMP  |APC       
 2|CUS  |WOLF      
 3|CUS  |SUESS     

EMPLOYEE

Id|PType|Job      |Sal    |HireDate  |DeptNo      
--+-----+---------+-------+----------+------
 1|EMP  |PLUMBER  |   3500|20-MAY-09 |    50   

CUSTOMER

Id|PType|Ref      |CreditRating   
--+-----+---------+------------
 2|CUS  |W/10/2   |AAA    
 3|CUS  |S/10/3   |AA     

The duplication of the PType column on the sub-type tables might seem a little strange. But it is useful for enforcing a strong foreign key between sub-type and super-type tables. The PERSON table has both a primary key on (ID) and a UNIQUE KEY on (ID,PTYPE). The sub-types have a CHECK constraint on PTYPE; for instance on CUSTOMER it would CHECK (ptype='CUS'). This means that the sub-types can have a foreign key on (ID,PTYPE) which ensures that records in CUSTOMER can only reference records in PERSON having a PTYPE of 'CUS'.

As for querying, you would probably want to do something like this to get employees:

select p.*
       , e.job
       , e.sal
       , e.hiredate
       , e.deptno
from person p
     inner join employee e
     on ( p.id = e.id
          and p.ptype = e.ptype )
/

And this to get customers:

select p.*
       , c.ref
       , c.creditrating
from person p
     inner join customer c
     on ( p.id = c.id
          and p.ptype = c.ptype )
/

Including the PTYPE in the join criteria is optional but its presence may help the database optimizer pick a better execution path.

It is possible to query all the PERSON records with their sub-type columns by using outer joins ...

select p.*
       , e.job
       , e.sal
       , e.hiredate
       , e.deptno
       , c.ref
       , c.creditrating
from person p
     left outer join employee e
     on ( p.id = e.id
          and p.ptype = e.ptype )
     left outer join customer c
     on ( p.id = c.id
          and p.ptype = c.ptype )
/

However, this is not something you ought to do lightly. With five such tables, the outer joins may generate very poor access paths. Separate queries might perform a lot faster than a single agglomerated query.