TSQL - Manager - Manager - Employee Recursive CTE Query

This post is based on my MSDN forum question. I have to implement this logic in one of procedures. Query Approach seems to be fine based on comments. Necessary foreign key is already in place. One of stackoverflow post was useful for me to arrive at solution.

Below is the solution: Input is Employee Id, Output is list of Employees working for the Manager.

CREATE TABLE Employee_Manager
( EmployeeId [int] NOT NULL,
  ManagerId [int] NULL)
  

INSERT INTO [dbo].[Employee_Manager] ([EmployeeId], [ManagerId])

VALUES (1,2),(3,1),(4,1),(5,4),(6,4)

SELECT * FROM [dbo].[Employee_Manager]

Manager - Employee

2       -   1
1       -   3
1       -   4
4       -   5
4       -   6

/* Return list of employee when managerid is passed as input */
DECLARE @MgrId [int]
SET @MgrId = 4
;WITH EmployeeHierarchy(ManagerID, EmployeeID)
AS
(
      SELECT [EM].[ManagerID], [EM].[EmployeeID]
      FROM [dbo].[Employee_Manager] EM
      WHERE [EM].[ManagerID] = @MgrId
      UNION ALL
      SELECT [EM].[EmployeeID], [EM].[EmployeeID]
      FROM [dbo].[Employee_Manager] EM
      INNER JOIN [EmployeeHierarchy] EH ON
      EM.[ManagerID] = [EH].[EmployeeID]
)   
SELECT [EMH].[EmployeeID], [EMH].[ManagerID]
FROM [EmployeeHierarchy] EMH

Please feel free to comment on this post for any better solution...

More Reads
T-SQL Tuesday #18 - CTEs - The permission hierarchy problem


Happy Learning!!!