Recursive CTE - Find All Employees Below Manager
I created a sample script for this SQLFIDDLE
CREATE TABLE [dbo].[Users](
[userId] [int] ,
[userName] [varchar](50) ,
[managerId] [int] ,
)
INSERT INTO dbo.Users
([userId], [userName], [managerId])
VALUES
(1,'Darry',NULL),
(2,'Cono',1),
(3,'Abros',2),
(4,'Natesh',1),
(5,'Ani',3),
(6,'Raju',5),
(7,'Pinky',5),
(8,'Miya',4)
My requirement is like displaying the hierarchy of all employees below this particular manager
Here is what I have tried
WITH UserCTE AS (
SELECT userId, userName, managerId, 0 AS EmpLevel
FROM Users where managerId IS NULL
UNION ALL
SELECT usr.userId, usr.userName, usr.managerId, mgr.[EmpLevel]+1
FROM Users AS usr
INNER JOIN UserCTE AS mgr
ON usr.managerId = mgr.userId where usr.managerId IS NOT NULL
)
SELECT *
FROM UserCTE AS u where u.ManagerId=3
ORDER BY EmpLevel;
Output:
userName
--------
Ani
The result I expect if I give the manager 3, then the following employees should be shown
1.Abros
2.Ani
3.Raju
4.Pinky
Can anyone help on this
+3
source to share
2 answers
Try it. The filter must be applied in Anchor query
CTE
WITH UserCTE
AS (SELECT userId,
userName,
managerId,
0 AS EmpLevel
FROM [Users]
WHERE managerId = 3
UNION ALL
SELECT usr.userId,
usr.userName,
usr.managerId,
mgr.[EmpLevel] + 1
FROM [Users] AS usr
INNER JOIN UserCTE AS mgr
ON usr.managerId = mgr.userId
WHERE usr.managerId IS NOT NULL)
SELECT *
FROM UserCTE AS u
ORDER BY EmpLevel;
FIDDLE DEMO
+6
source to share
Try this please
declare @managerId int
SET @managerId = 3
select userId
from Users as t1
where managerId = @managerId
or exists
( select *
from Users as t2
where userId = t1.managerId
and (
managerId = @managerId
or exists
( select *
from Users as t3
where userId = t2.managerId
and managerId = @managerId
)
)
)
-1
source to share