Get all child employee with its Level by using CTE
CTE stand for Common Table ExpressionCTE use for get recursive data
here we will learn how get all child employee recursively and also get its employment level
Here i have usermaster table with structure Like
Column_name Type
UserID int
UserName nvarchar(100)
Password nvarchar(400)
FirstName nvarchar(40)
LastName nvarchar(40)
Surname nvarchar(100)
Mobile nvarchar(32)
Email nvarchar(100)
ParentUserID int
-------------------------
Example :
-------------------------
;with cte(UserID,FirstName,ParentUserID,[Level])
as(
select UserID,FirstName,ParentUserID,0 as [Level] from usermaster where UserID = 1
union all
select u.UserID,u.FirstName,u.ParentUserID, c.[Level]+1 as [Level] from UserMaster u inner join cte c on c.UserID=u.ParentUserID
)
select * from cte
---------------------------
output
---------------------------
UserID FirstName ParentUserID Level
1 Jayendra 0 0
2 aaa 1 1
3 bbb 1 1
4 ccc 1 1
13 chaa 1 1
14 ccc 1 1
16 jay 3 2
12 chaa 2 2
15 chaa 2 2
