Recursive CTE with additional EXISTS conditions?

Question

I have a situation where I need to be able to see if a given person is within a user/manager hierarchy. I have the next structure of table: UserId UserName ManagerId

I have 2 IDs: some UserId (say 5) and ManagerId (say 2). As a result I need to know if manager with given Id (2) is chief for user with given id (5)? For example, if

1. User 1 reports to user 2.
2. User 3 reports to user 1.
3. User 4 reports to user 3

the result SQL-query have to show that for UserId = 4 and ManagerId = 1 answer is true.

I've just created query for getting all 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 result query with above mentioned conditions :(

Thanks in advance for any help!

Answer 1

Find the user in the anchor and walk your way back up the hierarchy. Check the rows you have got in the recursive query against the manager.

This will return the manager row if there exist one.

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