Recursive CTE Example: Hierarchy of Employees

 Finding the Hierarchy of Employees

Imagine you have an employees table with the following structure:

sql
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    manager_id INT
);

Here's a sample dataset:

sql
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);

You want to find the hierarchy of all employees starting from a specific manager (say, Alice).

sql
WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor member: start with the root manager
    SELECT 
        employee_id,
        employee_name,
        manager_id,
        1 AS level
    FROM 
        employees
    WHERE 
        employee_name = 'Alice'

    UNION ALL

    -- Recursive member: find employees who report to the current employee
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1
    FROM 
        employees e
    INNER JOIN 
        EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT 
    employee_id,
    employee_name,
    manager_id,
    level
FROM 
    EmployeeHierarchy
ORDER BY 
    level, employee_id;