Hierarchical Query: Parents parent child-CodePudding

I was trying to achieve the result as mentioned below using hierarchical queries in Oracle, tried various ways using CONNECT_BY_ROOT, CONNECT BY PRIOR,... but somehow not able to break into the desired result.

CREATE TABLE test (
    id int, 
    name varchar2(75),
    parentid int, 
    class int
    );


INSERT into test values (1,   'BOSS',null,null);
INSERT into test values (2,   'C1',1, 0);
INSERT into test values (34,  'C2',2, 1);
INSERT into test values (37,  'C3',34, 2);
INSERT into test values (50,  'C4',37, 2);
INSERT into test values (51,  'C5',50, 4);
INSERT into test values (100, 'C6',37, 4);
INSERT into test values (101, 'C7',37, 5);

In a query, if I pass 'C4's id=50 and class=4, then it should return its child i.e. C5 (whose parentid is 50) at the same time it should also fetch C6 (because this is C4's parent's child i.e. C4's parent is C3 and it got a child C6 i.e. parentid 37)

51  C5  50  -- This is Child of C4 i.e. C5's parent=50 
100 C6  37  -- This is because C4 parent is C3 and in turn C3 has another child C6 of same class

I started with some basic query and did some changes to fetch the desired result but no success...I know this is not correct...and missing some

SELECT distinct id, name, parentid
FROM test
CONNECT BY PRIOR id =parentid
START WITH id=50
order by 1 ;

(Edited Text)

SELECT 
distinct id, name, parentid,
    connect_by_root id          ,
    sys_connect_by_path(name,' > ') as full_path,
    CONNECT_BY_ROOT id cbr,
    CONNECT_BY_ISLEAF AS leaf    
FROM test
CONNECT BY PRIOR id =parentid
START WITH id=50
order by 1 ;

(Edited text) Expected Output :

51  C5  50 
100 C6  37

CodePudding user response：

Here is one solution : you need to use nocycle option in the connect by clause to make oracle stop when encountering loops.

select distinct ID, NAME, PARENTID
from test
where class = 4
start with name = 'C4'
connect by nocycle prior id = parentid or prior parentid = parentid
order by id
;

CodePudding user response：

If you want to select all direct or indirect childs of the parent of id = 50 use simple a recursive CTE

with rec_cte (ID, NAME, PARENTID, CLASS) as (
 select * from test
 where parentid = (select parentid from test where id = 50)
  union all
 select test.ID, test.NAME, test.PARENTID, test.CLASS
 from rec_cte
 join test on rec_cte.id = test.parentid
)
select * from rec_cte

which gives as expected

        ID NAME    PARENTID      CLASS
---------- ----- ---------- ----------
        50 C4            37          2
       100 C6            37          4
       101 C7            37          5
        51 C5            50          4

If you want to restrict the above result to records with class = 4 add a filtering predicate

with rec_cte (ID, NAME, PARENTID, CLASS) as (
 select * from test
 where parentid = (select parentid from test where id = 50)
  union all
 select test.ID, test.NAME, test.PARENTID, test.CLASS
 from rec_cte
 join test on rec_cte.id = test.parentid
)
select ID, NAME, PARENTID
from rec_cte
where class = 4

This produces the result you expects

        ID NAME    PARENTID
---------- ----- ----------
       100 C6            37
        51 C5            50