SQL Common Table Expressions recursively going up in a tree
Example
WITH RECURSIVE ManagersOfJonathon AS (
-- start with this row
SELECT *
FROM Employees
WHERE ID = 4
UNION ALL
-- get manager(s) of all previously selected rows
SELECT Employees.*
FROM Employees
JOIN ManagersOfJonathon
ON Employees.ID = ManagersOfJonathon.ManagerID
)
SELECT * FROM ManagersOfJonathon;
| Id | FName | LName | PhoneNumber | ManagerId | DepartmentId |
|---|---|---|---|---|---|
| 4 | Johnathon | Smith | 1212121212 | 2 | 1 |
| 2 | John | Johnson | 2468101214 | 1 | 1 |
| 1 | James | Smith | 1234567890 | NULL | 1 |
