Multiple tables joined to a table via single column

Question

I am trying to create query, on below scenario.

with my skills I am able to join Table A,A1,B and A,A1,C and A,A1,D individually and union them.

Is there any better way to achieve same. I am using Oracle as Database.

Answer 1

It all depends on what they mean and if you need to know the columns the values are from.

This would get all the columns and you would have NULL values from the non-matching B, C, D tables:

SELECT *
FROM   a1
       INNER JOIN a ON a1.aid = a.id
       LEFT OUTER JOIN b ON a.extid = b.extid
       LEFT OUTER JOIN c ON a.extid = c.extid
       LEFT OUTER JOIN d ON a.extid = d.extid

Or, this would get only the relevant values and give you the type they belong to in fewer columns:

SELECT *
FROM   a1
       INNER JOIN a ON a1.aid = a.id
       INNER JOIN (
         SELECT extid, 'B' AS type, pqr_col AS col1, qrs_col AS col2 FROM b
         UNION ALL
         SELECT extid, 'C', abc_col, bcd_col FROM c
         UNION ALL
         SELECT extid, 'D', xyz_col, yza_col FROM d
       ) bcd
       ON a.extid = bcd.extid