CODEWITHSUNDEEP
sql-servercommon-table-expressionrecursive-query
John Duncan
John Duncan

Reputation: 31

Recursive CTE with additional EXISTS conditions?

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!

Upvotes: 3

Views: 1901

Answers (3)

sgeddes
sgeddes

Reputation: 62851

I've included the hierarchy of all levels with the CTE that you can then use to query. Using this hierarchy, you can see all the managers of a given employee in a delimited column (might be useful for other calculations).

Give this a 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 is the Fiddle. I've used UserId = 4 and ManagerId = 1.

Good luck.

Upvotes: 0

Hogan
Hogan

Reputation: 70538

This will return the BossID if he or she exist:

WITH BOSSES AS 
(
    SELECT BossID
    FROM Employees
    WHERE EmployeeID = @uID

    UNION ALL

    SELECT E.BossID
    FROM Employees E 
    JOIN BOSSES B ON E.EmployeeID = B.BossID
)
SELECT *
FROM BOSSES 
WHEN BossID = @bID

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

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

Upvotes: 3

Related Questions