SQL

Query Hierarchical Data with Recursive Common Table Expressions (CTE)

Explore tree-like or hierarchical datasets, such as organizational charts or category structures, using powerful SQL Recursive CTEs.

WITH RECURSIVE EmployeeHierarchy AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL -- Start with top-level employees

  UNION ALL

  SELECT e.id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT id, name, manager_id, level
FROM EmployeeHierarchy
ORDER BY level, id;

How it works: This powerful query uses a Recursive Common Table Expression (CTE) to traverse and retrieve hierarchical data, such as an employee reporting structure. The `EmployeeHierarchy` CTE is defined with an anchor member (the base case, e.g., top-level managers) and a recursive member that repeatedly joins back to the CTE itself to build the full hierarchy, assigning a 'level' to each employee.

Query Hierarchical Data with Recursive Common Table Expressions (CTE)

Need help integrating this into your project?