CODEWITHSUNDEEP
sqloracle-databaserecursive-query
christohew
christohew

Reputation: 49

Recursive query to get entire hierarchy [ORACLE]

I'm new to recursive queries so apologies if this is a simple solution but I'm thinking it will be a bit harder.

I've been given a hierarchy table that has multiple levels of parent-child relationships defined and I need a way to show all possible relationships that one item can have. There are also circular relationships that I cannot get rid of - I am unsure if this will cause issues.

For example, the original data would look like this:

PN       INT
=====    =====
ABC1     ABC2
ABC1     ABC9
ABC2     ABC3
ABC3     ABC4
DEF1     DEF2
GHI1     GHI2
GHI2     GHI1

I need the result of the query to look like this:

PN       INT
=====    =====
ABC1     ABC2
ABC1     ABC9
ABC1     ABC3
ABC1     ABC4
ABC2     ABC3
ABC2     ABC4
ABC3     ABC4
DEF1     DEF2
GHI1     GHI2
GHI2     GHI1

My approach so far has been joining the table back on itself as many times as required until I have the entire relationship defined (ie. every single child/INT of the original parent/PN) but I'm hoping there is a better way using recursive queries.

Hoping this makes sense and thanks in advance!

Upvotes: 0

Views: 1485

Answers (1)

Chris Saxon
Chris Saxon

Reputation: 9875

Build the hierarchy for every row, returning the value for pn for the root rows.

You can do this with connect by using connect_by_root:

create table t (
  c1 varchar2(4), c2 varchar2(4)
);

insert into t values ( 'ABC1', 'ABC2' );
insert into t values ( 'ABC1', 'ABC9' );
insert into t values ( 'ABC2', 'ABC3' );
insert into t values ( 'ABC3', 'ABC4' );
insert into t values ( 'DEF1', 'DEF2' );
insert into t values ( 'GHI1', 'GHI2' );
insert into t values ( 'GHI2', 'GHI1' );
commit;

select * from (
  select distinct connect_by_root c1 rt, c2
  from   t
  connect by nocycle c1 = prior c2
)
where  rt <> c2
order  by rt, c2;

RT      C2     
ABC1    ABC2    
ABC1    ABC3    
ABC1    ABC4    
ABC1    ABC9    
ABC2    ABC3    
ABC2    ABC4    
ABC3    ABC4    
DEF1    DEF2    
GHI1    GHI2    
GHI2    GHI1

The nocycle clause detects loops and stops processing.

Or recursive with by selecting the value of the root on each iteration:

with tree ( c1, c2, rt ) as (
  select c1, c2, c1 from t
  union all
  select t.c1, t.c2, tree.rt 
  from   tree
  join   t 
  on     t.c1 = tree.c2
) cycle c1 set is_cycle to 'Y' default 'N'
  select rt, c2 
  from   tree
  where  is_cycle = 'N'
  and    rt <> c2
  order  by rt, c2;

RT      C2     
ABC1    ABC2    
ABC1    ABC3    
ABC1    ABC4    
ABC1    ABC9    
ABC2    ABC3    
ABC2    ABC4    
ABC3    ABC4    
DEF1    DEF2    
GHI1    GHI2    
GHI2    GHI1

The cycle clause detects loops.

Upvotes: 1

Related Questions