Part 86: Traversing Organizational Hierarchies with Recursive CTEs

Querying management reporting structures of arbitrary depth.

1. Generating Management Tree with Level Depth

WITH RECURSIVE OrgChart AS (
  -- Anchor: Find CEO (no manager)
  SELECT id, first_name, manager_id, 1 AS depth, CAST(first_name AS VARCHAR(1000)) AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: Find subordinates
  SELECT e.id, e.first_name, e.manager_id, o.depth + 1, CAST(o.path || ' -> ' || e.first_name AS VARCHAR(1000))
  FROM employees e
  JOIN OrgChart o ON e.manager_id = o.id
)
SELECT * FROM OrgChart ORDER BY depth, id;

Key Takeaways

• Recursive CTEs traverse hierarchical trees effortlessly regardless of depth.

In Part 87, we explore Bill of Materials (BOM) & Graph Traversal with Recursive CTEs!