Self Join for Hierarchical Data

-- Employee-manager hierarchy
SELECT
  e.employee_id,
  e.first_name AS employee,
  m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY m.first_name, e.first_name;

-- Find all reports (direct + indirect) using recursive CTE
WITH RECURSIVE org_tree AS (
  SELECT employee_id, first_name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.first_name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree ORDER BY depth, first_name;