How to query the concrete table when only the parent table id is known

Question

I have an inheritance relationship in my database, where I have a parent table (Table A) that is basically a sequence of auto-incrementing integers (Id column). From here, I have several tables (Table B,C & D) that also have an Id column, which is a foreign key to the Id column in Table A.

Now my question is, if I have the Id, what is the most efficient way I can go about getting the correct row from table B, C, or D?

EDIT: There will be only ONE row with the Id in either table B, C, or D (there will not be a matching row in the other 2).

Answer 1

With a join.

SELECT 
  a.id, b.somecol, c.somecol, d.somecol
FROM 
  sometable a
INNER JOIN 
  btable b on b.id = a.id
INNER JOIN
  ctable c on c.id = a.id
INNER JOIN
  dtable d on d.id = a.id

If you're not sure that one of the child tables contains data related to the parent table, use an outer join instead of an inner join.

Answer 2

You could use union queries ... i.e.:

select 'TABLE B', a_id from b where a_id = 1
UNION
select 'TABLE C', a_id from c where a_id = 1
UNION
select 'TABLE D', a_id from d where a_id = 1;

This would give you a list of tables with an a_id of 1, while also showing the table it resided in.

If all 3 tables have the same structure, you could do something similar:

select b.* from b where a_id = 1
UNION
select c.* from c where a_id = 1
UNION
select d.* from d where a_id = 1;