CODEWITHSUNDEEP
sqlpostgresqlrecursive-query
Black
Black

Reputation: 5367

postgres - with recursive

I expected the following to return all the tuples, resolving each parent in the hierarchy up to the top, but it only returns the lowest levels (whose ID is specified in the query). How do I return the whole tree for a given level_id?

create table level(
level_id int,
level_name text,
parent_level int);

 insert into level values (197,'child',177), (  177, 'parent', 3 ), (  2, 'grandparent',  null  );

WITH RECURSIVE recursetree(level_id, levelparent) AS (
 SELECT level_id, parent_level 
 FROM level 
 where level_id = 197

UNION ALL
SELECT t.level_id, t.parent_level
FROM level t, recursetree rt 
WHERE rt.level_id = t.parent_level
)

SELECT * FROM recursetree;

Upvotes: 3

Views: 3809

Answers (1)

mu is too short
mu is too short

Reputation: 434655

First of all, your (2, 'grandparent', null) should be (3, 'grandparent', null) if it really is a grandparent. Secondly, your (implicit) join condition in the recursive half of your query is backwards, you want to get the parent out of rt.levelparent rather than t.parent_level:

WITH RECURSIVE recursetree(level_id, levelparent) AS (
    SELECT level_id, parent_level 
    FROM level 
    WHERE level_id = 197

    UNION ALL

    SELECT t.level_id, t.parent_level
    FROM level t JOIN recursetree rt ON rt.levelparent = t.level_id
    -- join condition fixed and ANSI-ified above
)
SELECT * FROM recursetree;

Upvotes: 9

Related Questions