biswajit kar biswajit kar - 4 months ago 16
SQL Question

Self join on two diff col of a same table

I have a following table

HQ1 HQ2



02400 02430

02430 02465

02430 02466

02465 02475

02466 02478

Here HQ1 is associated with HQ2 i.e 02400 is directly associated with 02430 and indirectly associated with 02430 02465 02466 02475 02478. How can i get all the association for 02400 in a single query? I have tried joining the same table but it doesn't give me all the associated values.

Answer

may be this help you, build the TREE

with a as (
         -- your data example
         select '02400' HQ1,'02430' HQ2 from dual union 
         select '02430', '02465' HQ2 from dual union 
         select '02430', '02466' HQ2 from dual union 
         select '02465', '02475' HQ2 from dual union 
         select '02466', '02478' HQ2 from dual

         -- another data example in same table
         union 
         select '02461', '02479' HQ2 from dual
         union 
         select '02479', '02478' HQ2 from dual
         union 
         select '02461', '0247X' HQ2 from dual
         )

select a.HQ2, level tree_level
  from a
 start with HQ1 = '02400' 
CONNECT BY PRIOR HQ2 = HQ1

you may select where HQ1 = '02461' to get other data.