I have a table like this:
| ID_employee | ID_Manager |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 3 |
| 6 | 5 |
| 7 | 5 |
| 8 | 5 |
(1 is a boss)
What I would like is a result like:
| ID_employee | ID_Manager |
|---|---|
| 8 | 5 |
| 8 | 3 |
| 8 | 1 |
| 7 | 5 |
| 7 | 3 |
| 7 | 1 |
| 6 | 5 |
| 6 | 3 |
| 6 | 1 |
| 5 | 3 |
| 5 | 1 |
| 4 | 2 |
| 4 | 1 |
I tried to use recursion but don’t print all manager of one element, only first
this is my query:
with ManagerHierarchy AS (
select ID_employee,
ID_manager
FROM HierarchyResource
UNION ALL
select HR.ID_employee, HR.ID_manager
from ManagerHierarchy inner join HierarchyResource as HR on ManagerHierarchy.ID_manager= HR.ID_employee
where HR.ID_manager!= HR.ID_employee
)
Select ID_employee, ID_manager from ManagerHierarchy
OPTION (MAXRECURSION 10);
for me the problem is that asshole of my boss have ID_Manager himself.