-- return everyone under Program Manager (ParentID = 8)WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)AS( SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree FROM MyDepartment WHERE ParentID = 8 UNION ALL SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1 FROM MyDepartment JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID)SELECT * FROM OrgTree;-- return Vice President (DepartmentID = 4) and direct reports (ParentID = 4)WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)AS( SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree FROM MyDepartment WHERE DepartmentID = 4 UNION ALL SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1 FROM MyDepartment JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID WHERE MyDepartment.ParentID = 4)SELECT * FROM OrgTree; -- return everyone above Senior Manager (DepartmentID = 6)WITH OrgTree(DepartmentName,ParentID,ReportsTo)AS( SELECT T1.DepartmentName,T2.DepartmentID,T2.DepartmentName FROM MyDepartment T1 INNER JOIN MyDepartment T2 ON T1.ParentID=T2.DepartmentID WHERE T1.DepartmentID=6 UNION ALL SELECT OT.ReportsTo,T2.DepartmentID,T2.DepartmentName FROM OrgTree OT INNER JOIN MyDepartment T1 ON OT.ParentID=T1.DepartmentID INNER JOIN MyDepartment T2 ON T1.ParentID=T2.DepartmentID)SELECT * FROM OrgTree;-- return list with of people with no direct reportsWITH OrgTree(ParentID, DepartmentID, DepartmentName, DepartmentLevel) AS ( SELECT ParentID, DepartmentID, DepartmentName, 0 AS DepartmentLevell FROM MyDepartment WHERE ParentID IS NULL UNION ALL SELECT e.ParentID, e.DepartmentID, e.DepartmentName, DepartmentLevel + 1 FROM MyDepartment AS e INNER JOIN OrgTree AS d ON e.ParentID = d.DepartmentID )SELECT * FROM OrgTree WHERE DepartmentLevel = 5;
