I created a sample fiddle 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 all employees hierarchy below that particular manager
Here is what i 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 output that i am expecting is, if i give a ManagerId 3,then following employees should be shown
1.Abros
2.Ani
3.Raju
4.Pinky
Can anyone help on this
Try this. Filter has to be applied in the Anchor query
of 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;