Recursive SQL Query

Recursive SQL Query

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 – Table of Employee

Now if I want all employees’ who are reporting to John like shown as below:

SQL Query to get desire output-5-2
Recursive SQL Query output

I have written below code by Using CTE. I have added e.MANAGER_ID=3.

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)
AS
(
SELECT e.ID,e.NAME, e.MANAGER_ID
FROM #TABLE e 
WHERE e.MANAGER_ID=3
UNION ALL
SELECT e.ID,e.NAME, e.MANAGER_ID
FROM #TABLE e 
INNER JOIN T m  
ON e.MANAGER_ID = m.ID 
)
SELECT * FROM T
WHERE MANAGER_ID IS NOT NULL
DROP TABLE #TABLE

Please share either  way to achieve same output using sql.

Leave a Reply