Multiple tables joined to a table via single column-CodePudding

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.

CodePudding user response：

Union was my first thought when I read your question. Though, for simplicity, you could first create a view and then join it to other table(s):

create view v_ext as
  select b.extid, b.pqr_col, b.qrs_col from b
  union all
  select c.extid, c.abc_col, c.bcd_col from c
  union all
  select d.extid, d.xyz_col, d.yza_col from d;
  
select *
from a join a1 on a.id = a1.aid
       join v_ext v on v.extid = a.extid;

CodePudding user response：

you can try the query with 'with' clause. Something like below, I havent tested it though

with union_output as
  ( select b.extid, b.pqr_col, b.qrs_col from b
    union
    select c.extid, c.abc_col, c.bcd_col from c
    union
    select d.extid, d.xyz_col, d.yza_col from d) 
select *
from a join a1 on a.id = a1.aid
       join union_output uo on uo.extid = a.extid;

CodePudding user response：

Select *from tableA A
Inner join tableA1 A1 on A1.A1ID=A.AID 
Inner join tableB b on b.ExtID=A.ExtID
Inner join tableC c on c.ExtID=A.ExtID
Inner join tableD d on d.ExtID=A.ExtID

CodePudding user response：

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