By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE employees (
EmployeeId int,
ManagerId int,
Name varchar(20)
);
insert into employees values
(1, NULL, 'TheGeneral'),
(2, 1, 'Bob'),
(3, 1, 'Christelle'),
(4, 1, 'Wilfer'),
(5, 2, 'Hailey'),
(6, 2, 'George'),
(7, 3, 'Mary'),
(8, 4, 'Henry'),
(9, 5, 'Wendy');
Records: 9 Duplicates: 0 Warnings: 0
WITH RECURSIVE employee_paths (EmployeeId, name, path, level) AS
(
SELECT EmployeeId, name, CAST(EmployeeId AS CHAR(200)), 1
FROM employees
WHERE ManagerId IS NULL
UNION ALL
SELECT e.EmployeeId, e.name, CONCAT(ep.path, ',', e.EmployeeId), level+1
FROM employee_paths AS ep JOIN employees AS e
ON ep.EmployeeId = e.ManagerId
)
SELECT * FROM employee_paths ORDER BY path;
| EmployeeId | name | path | level |
|---|---|---|---|
| 1 | TheGeneral | 1 | 1 |
| 2 | Bob | 1,2 | 2 |
| 5 | Hailey | 1,2,5 | 3 |
| 9 | Wendy | 1,2,5,9 | 4 |
| 6 | George | 1,2,6 | 3 |
| 3 | Christelle | 1,3 | 2 |
| 7 | Mary | 1,3,7 | 3 |
| 4 | Wilfer | 1,4 | 2 |
| 8 | Henry | 1,4,8 | 3 |
WITH RECURSIVE employee_paths (EmployeeId, name, path, level) AS
(
SELECT EmployeeId, name, CAST(EmployeeId AS CHAR(200)), 1
FROM employees
WHERE ManagerId IS NULL
UNION ALL
SELECT e.EmployeeId, e.name, CONCAT(ep.path, ',', e.EmployeeId), level+1
FROM employee_paths AS ep JOIN employees AS e
ON ep.EmployeeId = e.ManagerId
)
SELECT j.ReportIntoManagerId, t2.level as ManagerialLevel, t.EmployeeId, t.name
FROM employee_paths t
CROSS JOIN JSON_TABLE(CONCAT('["', REPLACE(t.path, ',', '","'), '"]'),
'$[*]' COLUMNS (ReportIntoManagerId TEXT PATH '$')
) j
INNER JOIN employee_paths t2 ON t2.EmployeeId = j.ReportIntoManagerId
WHERE j.ReportIntoManagerId <> t.EmployeeId OR t.EmployeeId = 1
order by ReportIntoManagerId
| ReportIntoManagerId | ManagerialLevel | EmployeeId | name |
|---|---|---|---|
| 1 | 1 | 1 | TheGeneral |
| 1 | 1 | 2 | Bob |
| 1 | 1 | 3 | Christelle |
| 1 | 1 | 4 | Wilfer |
| 1 | 1 | 5 | Hailey |
| 1 | 1 | 6 | George |
| 1 | 1 | 7 | Mary |
| 1 | 1 | 8 | Henry |
| 1 | 1 | 9 | Wendy |
| 2 | 2 | 5 | Hailey |
| 2 | 2 | 6 | George |
| 2 | 2 | 9 | Wendy |
| 3 | 2 | 7 | Mary |
| 4 | 2 | 8 | Henry |
| 5 | 3 | 9 | Wendy |