Example
WITH RECURSIVE ManagedByJames(Level, ID, FName, LName) AS (
-- start with this row
SELECT 1, ID, FName, LName
FROM Employees
WHERE ID = 1
UNION ALL
-- get employees that have any of the previously selected rows as manager
SELECT ManagedByJames.Level + 1,
Employees.ID,
Employees.FName,
Employees.LName
FROM Employees
JOIN ManagedByJames
ON Employees.ManagerID = ManagedByJames.ID
ORDER BY 1 DESC -- depth-first search
)
SELECT * FROM ManagedByJames;
| Level | ID | FName | LName |
|---|
| 1 | 1 | James | Smith |
| 2 | 2 | John | Johnson |
| 3 | 4 | Johnathon | Smith |
| 2 | 3 | Michael | Williams |
