Search code examples
sqloracle-database

Join a column in a table to multiple columns in another table

I have 2 tables in first table, I have a column(GDU) which contain hierarchical data like group, dept, unit in second table I have G, D, U (Group,Dept,Unit) as separate columns.

common key is GDU which I want to connect other table to recognize/segregate GDU to group, dept, unit

enter image description here

enter image description here

enter image description here

I tried joining T1 & T2 on group then dept then unit, but it did not work out,

SELECT A2.*,B2.U 
FROM 
    (SELECT A1.*, B1.D 
     FROM 
         (SELECT A.*, B.G 
          FROM T1 A 
          LEFT JOIN T2 B ON A.GDU = B.G) A1
     LEFT JOIN 
         T2 B1 ON A1.GDU = B1.G) A2
LEFT JOIN 
    T2 B2 ;

Any help would be much appreciated.

Solution

  • One option is to use self-outer join ("self" because t2 is being used 3 times; once for G, once for D and once for U):

    Sample data (t1 contains additional rn column, just for sorting purposes):

    SQL> with
  2  t1 (rn, gdu, amount) as
  3    (select 1, 'A' ,   100 from dual union all
  4     select 2, 'B' ,   150 from dual union all
  5     select 3, 'C' ,   125 from dual union all
  6     select 4, 'A1',   500 from dual union all
  7     select 5, 'B1',   800 from dual union all
  8     select 6, 'B2', 15000 from dual union all
  9     select 7, 'C2', 10000 from dual
 10    ),
 11  t2 (g, d, u) as
 12    (select 'A', 'A1', null from dual union all
 13     select 'B', 'B1', 'B2' from dual union all
 14     select 'C', 'C1', 'C2' from dual
 15    )
 16  --

    Query begins here:

     17  select a.gdu, b.g, c.d, d.u, a.amount
 18  from t1 a left join t2 b on a.gdu = b.g
 19            left join t2 c on a.gdu = c.d
 20            left join t2 d on a.gdu = d.u
 21  order by a.rn;

GDU   G     D     U         AMOUNT
----- ----- ----- ----- ----------
A     A                        100
B     B                        150
C     C                        125
A1          A1                 500
B1          B1                 800
B2                B2         15000
C2                C2         10000

7 rows selected.

SQL>