— SQL recursive CTE to find subordinates of executive, manager & supervisor– Tree processing – find all descendants – find children – self-referencing tableDECLARE @EmployeeID INT SET @EmployeeID = 109 USE AdventureWorks; WITH cteEmployeeName AS (SELECT FullName = FirstName + ‘ ‘ + LastName, EmployeeID FROM HumanResources.Employee e INNER JOIN Person.Contact c ON e.ContactID = c.ContactID), cteSubordinates AS (SELECT EmployeeID, LEVEL = 0 FROM HumanResources.Employee WHERE EmployeeID = @EmployeeID UNION ALL SELECT e.EmployeeID, LEVEL + 1 FROM cteSubordinates cte INNER JOIN HumanResources.Employee e ON cte.EmployeeID = e.ManagerID) SELECT FullName, s.EmployeeID,…
Building of an organizational chart for AdventureWorks & AdventureWorks2008
Read More