Discussion on: #SQL30 Day 5: Hierarchies

View post

found a bit more time! have known about recursive queries for a while but never had to use them outside of exercises like this.

the direct answer to your question would be something like:

WITH RECURSIVE tree AS (
    SELECT * FROM employee WHERE employee_id = 1
  UNION ALL
    SELECT p.*
    FROM employee p
      JOIN tree c ON p.employee_id = c.manager_id
)
SELECT employee_id, name FROM tree;

but it's more interesting to handle the case of more than one employee, and how you do something sensible with the results. if you're expecting this to be handled by code that's good with trees/graphs like this then, the following seems nice:

WITH RECURSIVE tree AS (
    SELECT * FROM employee WHERE employee_id IN (1, 6)
  UNION
    SELECT p.*
    FROM employee p, tree c
    WHERE p.employee_id = c.manager_id  -- gratuitous change of syntax!
)
SELECT employee_id, name, manager_id FROM tree;

the only meaningful change is to use a UNION instead of a UNION ALL, but suppose a plain UNION could have been used in the first query.

if you wanted something that's directly nice for presentation then it might be useful to track the path up the tree:

WITH RECURSIVE tree AS (
    SELECT employee_id AS leaf_employee_id, 0 AS depth, * FROM employee
  UNION ALL
    SELECT leaf_employee_id, depth + 1, p.*
    FROM employee p, tree c
    WHERE p.employee_id = c.manager_id
)
SELECT leaf_employee_id, depth, employee_id, name
FROM tree
WHERE leaf_employee_id IN (1, 2)
ORDER BY 1, 2;