Recursive SQL Query-2
Lets say I have a table which contains list of Employees of company, which is as shown below:

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

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.