Parent child Relationship



Consider the below Parent_Child Table, where A is the parent of B and C and B is the parent of D and E and so on..


















Expected output:




















Query :


with A(Element,lev) as
(
select c1,0 from parent_child union
select c2,1 from parent_child union
select c3,2 from parent_child union
select c4,3 from parent_child
),

Rel(parent,child) as
(
select distinct c1,c2 from parent_child union
select distinct c2,c3 from parent_child union
select distinct c3,c4 from parent_child
)

select
row_number() over(order by lev) as id,
A.Element,
A.lev,
(select parent from rel where child=A.Element) as parent_id
from A


-----------------------Table creation Queries---------------------------------------------------------------------


create table parent_child
(
c1 varchar2(5),
c2 varchar2(5),
c3 varchar2(5),
c4 varchar2(5)
);

insert into parent_child values('A','B','D','H');
insert into parent_child values('A','B','D','I');
insert into parent_child values('A','B','E',NULL);
insert into parent_child values('A','C','F',NULL);
insert into parent_child values('A','C','G',NULL);

commit;

No comments:

Post a Comment

Unanswered questions

why packages? global variables loads to buffer memory thereby performance Dimensional databses - star and snowflake schema Fact and dimensi...