SQL | Get row from table where I have parent and child nodes

user_14628169 0
Viewed 26

I have a single table with both parent and child nodes.

Table:

ID Parent Child Level Description
100 NULL 10 1 Level 1
200 10 20 2 Level 2
300 20 30 3 Level 3
400 30 40 4 Level 4
101 NULL 11 1 Lvl 1
201 11 21 2 Lvl 2

From another query I get a row with the information related to the last level of my table. For example:

ID Field1 Field2 Parent Child Level Description
1 123 456 30 40 4 Level 4

I need to get the information related to the other levels. I want to get a result set like:

ID Field1 Field2 Level Description
1 123 456 4 Level 4
1 123 456 3 Level 3
1 123 456 2 Level 2
1 123 456 1 Level 1

Important: I never know how many levels there are for each case. Also, I only get the values of Field1 and Field2 for the last level.

mysql sql
1 Answers

You can use a recursive cte to walk the tree from a given level to the root:

with recursive cte as (
   select t.* from tbl t where t.level = 4
   union all
   select t.* from cte c join tbl t on t.child = c.parent
)
select f.id, f.field1, f.field2, c.level, c.description 
from cte c cross join fields f where f.level = 4

See fiddle.

user_73267380
Related