Recursive query for hirarchical data based on adjacency list

Question

Learing SQL, and have a bit of a problem. I have 2 tables level and level_hierarchy

|name        | id |     |parent_id | child_id|
-------------------     ---------------------
| Level1_a   | 1  |     | NULL     |    1    |
| Level2_a   | 19 |     | 1        |    19   |
| Level2_b   | 3  |     | 1        |    3    |
| Level3_a   | 4  |     | 3        |    4    |
| Level3_b   | 5  |     | 3        |    5    | 
| Level4_a   | 6  |     | 5        |    6    | 
| Level4_b   | 7  |     | 5        |    7    |

Now what I need, is a query that will return all entries from table level from every hirarchy level based on parameter that marks what level hierarchy level I want to get entries from.

Getting Level1 entries is quite easy.

SELECT name FROM level INNER JOIN level_hierarchy ON level.id = 
level_hierarchy.child_id WHERE level_hierarchy.parent_id=NULL

Level2 entries:

Level2_a
Level2_b

are just the ones that have a parent and the parent of their parent is NULL and so on. This is where I suspect that recursion comes in.

Is there anyone who can guide thorugh it?

klin · Accepted Answer · 2018-07-15 19:20:50Z

7

Your query for the first level (here depth to distinguish from the table) should look like this:

select l.name, h.child_id, 1 as depth 
from level l
join level_hierarchy h on l.id = h.child_id 
where h.parent_id is null;

   name   | child_id | depth 
----------+----------+-------
 Level1_a |        1 |     1
(1 row)

Note the proper use of is null (do not use = to compare with null as it always gives null).

You can use the above as an initial query in a recursive cte:

with recursive recursive_query as (
    select l.name, h.child_id, 1 as depth 
    from level l
    join level_hierarchy h on l.id = h.child_id 
    where h.parent_id is null
union all
    select l.name, h.child_id, depth + 1
    from level l
    join level_hierarchy h on l.id = h.child_id
    join recursive_query r on h.parent_id = r.child_id
)
select *
from recursive_query
-- where depth = 2

   name   | child_id | depth 
----------+----------+-------
 Level1_a |        1 |     1
 Level2_b |        3 |     2
 Level2_a |       19 |     2
 Level3_a |        4 |     3
 Level3_b |        5 |     3
 Level4_a |        6 |     4
 Level4_b |        7 |     4
(7 rows)

answered Jul 15, 2018 at 19:20

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zerodf Over a year ago

I like the depth field, that really makes things clear!

Yemi Kudaisi Over a year ago

Please how do I modify this to get all the parents of child given the child's id ?

klin Over a year ago

@YemiKudaisi - see Recursive CTE concatenate fields with parents from arbitrary point

Zerodf · Accepted Answer · 2018-07-15 19:05:32Z

4

Good question, recursion is a difficult topic in SQL and its implementation varies by engine. Thanks for tagging your post with PostgreSQL. PostgreSQL has some excellent documentation on the topic.

WITH RECURSIVE rec_lh(child_id, parent_id) AS (
    SELECT child_id, parent_id FROM level_hierarchy
  UNION ALL
    SELECT lh.child_id, lh.parent_id
    FROM rec_lh rlh INNER JOIN level_hierarchy lh
      ON lh.parent_id = rlh.child_id
  )
SELECT DISTINCT level.name, child_id 
FROM rec_lh INNER JOIN level
  ON rec_lh.parent_id = level.id
ORDER BY level.name ASC;

See Also:

Recursive query in PostgreSQL. SELECT *

answered Jul 15, 2018 at 19:05

Zerodf

2,30820 silver badges26 bronze badges

Collectives™ on Stack Overflow

Recursive query for hirarchical data based on adjacency list

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related