Recursive SQL Query-2

Recursive SQL Query-2 

Lets say I have a table which contains list of Employees of company, which is as shown below:

SQL Query to get desire output-1
Recursive SQL Query -2- Table of Employee

Now if I want all employees’ who are working on 3rd level of hierarchy  like shown as below:

Recursive Sql Query-2- output
Recursive Sql Query-2- output

I have written below code by Using CTE. I have added Level for each hierarchy and fetch 3rd level as shown below query .

CREATE TABLE #TABLE 
	(ID INT , NAME VARCHAR(100), MANAGER_ID INT )

INSERT INTO #TABLE
VALUES(1 , 'Jim',NULL),
(2 , 'Pole', 1),
(3 , 'John', 1),
(4 , 'Crook', 3), 
(5 , 'Sara', 4)

SELECT * FROM #TABLE

;WITH T(ID,NAME,MANAGER_ID, EmployeeLevel)
AS
(
SELECT e.ID,e.NAME, e.MANAGER_ID,0 + 1 AS EmployeeLevel
FROM #TABLE e 
WHERE e.MANAGER_ID IS NULL
UNION ALL
SELECT e.ID,e.NAME, e.MANAGER_ID, m.EmployeeLevel + 1 AS EmployeeLevel
FROM #TABLE e 
INNER JOIN T m  
ON e.MANAGER_ID = m.ID 
)
SELECT * FROM T
WHERE EmployeeLevel=3
DROP TABLE #TABLE

Please share either  way to achieve same output using sql.

Leave a Reply