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.
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
Answered By – MT0
Answer Checked By – Jay B. (BugsFixing Admin)