Sql query to find the all subordinates under a single superior

Question

My Table contains Three columns and the value looks like the following

Emp_ID |  Emp_Name   |  Emp_Manager_ID
========================================
1      |  Admin      |   Null         
2      |  John       |   1            
3      |  Sam        |   2             
4      |  Mike       |   2            
5      |  Jeff       |   4            
6      |  Ben        |   3            
7      |  Vicky      |   5

The parameter id @Emp_ID = 2 The expected result to find all the subordinates under the given Emp_Id so the result should be all EmpIDs 3,4,5,6,7 because 2 is the manager of 3,4 and 3 is the manager of 6, 4 is the manager of 5 and 5 is the manager of 7

Answer 1

Using a Recursing CTE. This currently returns all three columns. Remove Emp_Name and Emp_Manager_ID from the SELECT if you do not require that information.

WITH Subordinates AS
(
   SELECT e.Emp_ID, e.Emp_Name, e.Emp_Manager_ID
   FROM Employee AS e
   WHERE e.Emp_Manager_ID = 2
   
   UNION ALL

   SELECT e.Emp_ID, e.Emp_Name, e.Emp_Manager_ID
   FROM Employee AS e
   INNER JOIN Subordinates AS sub ON e.Emp_Manager_ID = sub.Emp_ID
)
SELECT s.Emp_ID, s.Emp_Name, s.Emp_Manager_ID
FROM Subordinates AS s

Example of query running using Employee_ID = 1: