Find all descendants of a parent in sql, including descendants of descendants-CodePudding

I want to find a table like this:

Parent	Child
A	B
A	C
A	D
A	E
B	C
B	D

So, the first level under A are nodes B and C. And under B is another level of nodes including C and D, however I also want these nodes included as rows under A.

The available dataset consists out of nodes with their level.

Node	Level
A	1
B	2
C	3
D	3
E	2

And all the children below B are the rows until E as B and E have the same level.

Is it possible to create a table as I wanted from the information I have? I have already looked into stored procedures but I have not used these before so I am a bit lost.

CodePudding user response：

You may try the following self-join query:

select D.node Parent, T.node Child
from table_name T join table_name D
on T.level > D.level
order by D.node, T.node

If you want to select only one node from the nodes with the same level you may use the ROW_NUMBER() function as the following:

select D.node Parent, T.node Child
from table_name T join
  (
   select *, 
   row_number() over (partition by level order by node) rn 
   from table_name
  ) D
on T.level > D.level
where D.rn = 1
order by D.node, T.node

See a demo.