CREATE TABLE Employee ( ID INT, FirstName VARCHAR(50), LastName VARCHAR(50), DepartmentID INT, ReportToID INT ) CREATE Table Departments ( ID INT, DepartmentName VARCHAR(50) ) INSERT INTO Employee VALUES (1, 'F1', 'L1', 1, NULL); INSERT INTO Employee VALUES (2, 'F2', 'L2', 2, 1); INSERT INTO Employee VALUES (3, 'F3', 'L3', 1, 2); INSERT INTO Employee VALUES (4, 'F4', 'L4', 2, 3); INSERT INTO Employee VALUES (5, 'F5', 'L5', 1, NULL); INSERT INTO Employee VALUES (6, 'F6', 'L6', 2, 1); INSERT INTO Employee VALUES (7, 'F7', 'L7', 1, 2); INSERT INTO Departments VALUES ( 1 , 'D1'); INSERT INTO Departments VALUES ( 2 , 'D2'); INSERT INTO Departments VALUES ( 3 , 'D3'); SELECT * FROM Employee SELECT * FROM Departments ;WITH cte AS ( SELECT ID,firstname,lastname,departmentid,reporttoid FROM Employee WHERE FirstName='F2' -- Change this UNION ALL SELECT a.ID,a.firstname,a.lastname,a.departmentid,a.reporttoid FROM Employee a INNER JOIN cte b ON a.ReportToID=b.Id ),cte1 AS ( SELECT CTE.iD AS EmployeeId ,e.Id AS ManagerId ,cte.FirstName AS EmployeeFirstName ,cte.LastName AS EmployeeLastName ,e.FirstName AS ManagerFirstName ,e.LastName AS ManagerLastName ,cte.DepartmentId AS EmpDepId--Emp ,e.DepartmentId AS MgrDepId-- Mgr FROM cte CROSS APPLY Employee e WHERE cte.Reporttoid=e.ID ),cte2 AS ( SELECT cte.* ,d1.DepartmentName AS EmployeeDepartmentName FROM cte1 AS cte LEFT JOIN Departments AS d1 ON cte.EmpDepId=d1.ID ) SELECT cte2.EmployeeId ,cte2.ManagerId ,cte2.EmployeeFirstName ,cte2.EmployeeLastName ,cte2.ManagerFirstName ,cte2.ManagerLastName ,cte2.EmployeeDepartmentName ,d2.DepartmentName AS ManagerDepartmentName FROM cte2 LEFT JOIN Departments AS d2 ON cte2.MgrDepId=d2.Id