Recursive CTE with EXISTS Additional Conditions?
I have a situation where I need to be able to see if a given person is in the user / manager hierarchy. I have the following table structure: User ID UserName ManagerID
I have 2 IDs: some UserId (say 5) and ManagerId (say 2). As a result, I need to know if the manager with the given Id (2) is the main one for the user with the given identifier (5)? For example, if
- User 1 informs User 2.
- User 3 informs User 1.
- User 4 informs User 3
As a result, the SQL query should show that the answer is correct for UserId = 4 and ManagerId = 1.
I just created a query to get the entire hierarchy:
WITH temp (level, UserName, UserId, ManagerId) AS
(
SELECT 1 AS level, EmployeeName, EmployeeId, BossId
FROM Employees
WHERE BossId IS NULL
UNION ALL
SELECT level+1 AS level, EmployeeName, EmployeeId, BossId
FROM Employees, temp
WHERE BossId = UserId
)
SELECT t.* from temp AS t
But now I don't know how to get the query result with the above conditions :(
Thanks in advance for your help!
source to share
Find the user in the binding and walk back through the hierarchy. Check the lines you have in the recursive query with the manager.
This will return the manager string if it exists.
WITH temp AS
(
SELECT EmployeeName, EmployeeId, BossId
FROM Employees
WHERE EmployeeId = @UserID
UNION ALL
SELECT E.EmployeeName, E.EmployeeId, E.BossId
FROM Employees AS E
inner join temp AS T
ON E.EmployeeId = T.BossId
)
SELECT *
FROM temp
WHERE EmployeeId = @ManagerID
source to share
I have included a hierarchy of all levels with a CTE, which you can then use to query. Using this hierarchy, you can see all the managers of a given employee in a separator column (may be useful for other calculations).
Try:
WITH cte (UserId, ManagerId, Level, Hierarchy) as (
SELECT EmployeeId, BossId, 0, CAST(EmployeeId as nvarchar)
FROM Employee
WHERE BossId IS NULL
UNION ALL
SELECT EmployeeId, BossId, Level+1,
CAST(cte.Hierarchy + '-' + CAST(EmployeeId as nvarchar) as nvarchar)
FROM Employee INNER JOIN cte ON Employee.BossId=cte.UserId
)
SELECT *
FROM cte
WHERE UserId = 4
AND '-' + Hierarchy LIKE '%-1-%'
And here's the Fiddle . I used UserId = 4 and ManagerId = 1.
Good luck.
source to share