Wandering around, playing around, then problems come. I tried to crack the level concept . So I followed through with the most voted answer from the above link. The following is my code sample data.
begin; create temp table tempemp (employee_id integer primary key, last_name text,manager_id integer); insert into tempemp values(1,'eliane',1); insert into tempemp values(2,'sponge',1); insert into tempemp values(3,'george',1); insert into tempemp values(4,'kramer',2); insert into tempemp values(5,'megan',2); insert into tempemp values(6,'donald',3); commit;
WITH RECURSIVE cte AS ( SELECT employee_id, last_name, manager_id, 1 AS level FROM tempemp
UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, c.level + 1 FROM cte c JOIN tempemp e ON e.manager_id = c.employee_id ) SELECT * FROM cte;
But when I execute the code, It seems like an infinite loop. So, any suggestions(code sample) for me to crack the level concept?