Get all child or sub employee with its Level by using CTE

Get all child employee with its Level by using CTE

CTE stand for Common Table Expression

CTE 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

print 1 to 100 by CTE

CTE

How to print 1 to 100 in sql by using CTE?

CTE stand for Common Table Expression

CTE use for get recursive data

here we will learn how to print 1 to 100 in single select statement.

-------------------------
Example :
-------------------------
;with cte (no)
as(
select 1 as no
union all
select no+1 from cte where no < 100
) select no from cte
---------------------------
output
---------------------------
no
1
2
3
4
5
6
7
.
.
.

100