1
SQLIntermediate#views
SQLAdvanced#cte
How do you write a recursive CTE?
Use WITH RECURSIVE with an anchor member (the starting rows) UNION ALL a recursive member that references the CTE, and a condition that terminates it.
Example
WITH RECURSIVE org AS ( SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e JOIN org o ON e.manager_id = o.id ) SELECT * FROM org;