postgres - with recursive

Question

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;

Answer 1

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;

• Michal Zimmermann: How do I get all the children on the same level? Right now I get only one row on each level.
• mu is too short: @zimmi: Not sure what you mean.
• Michal Zimmermann: Let's say I have two rows which have the same parent_id. With this piece of code, I get only one of them in the result set.
• Michal Zimmermann: Have a look at sqlfiddle.com/#!15/ac381/1. You should get User, Manager1 and Manager2 as a result, but you only get one of managers.
• mu is too short: @zimmi: But there's nothing in there links to Manager2. The links go Admin -> Manager1 -> User, there's nothing in there that points toward Manager2 so the Manager2 -> User link will not be followed.