USE AdventureWorks ;
GO
WITH DirectReports(LoginID, ManagerID, EmployeeID) AS
(
SELECT LoginID, ManagerID, EmployeeID
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.LoginID, e.ManagerID, e.EmployeeID
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT *
FROM DirectReports;
OPTION (MAXRECURSION 3);
GO
WITH DirectReports(LoginID, ManagerID, EmployeeID) AS
(
SELECT LoginID, ManagerID, EmployeeID
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.LoginID, e.ManagerID, e.EmployeeID
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT *
FROM DirectReports;
OPTION (MAXRECURSION 3);