Recursive TSQL (Employees ‐ Manager sample) - FalconFT/FalconDocs GitHub Wiki

How to create a SQL sentence based on Employees table, which has a hierarchy based on EmployeeId and ManagerId.

EmployeeId is unique, represents the ID of an employee.

ManagerId can be NULL if the employee has no manager, and if not, it's the EmployeeId of the manager.

image

DECLARE @EmployeeId INT = 11 --Employee and all its descendants

;with cte as 
(
    SELECT 
		E.EmployeeId, E.EmployeeName, E.ManagerId, E.IsEnabled, 1 AS IsTheBoss
		FROM hr.Employees E
		WHERE E.EmployeeId = @EmployeeId
    UNION all
    SELECT E.EmployeeId, E.EmployeeName, E.ManagerId, E.IsEnabled, 0 AS IsTheBoss
	FROM cte 
        inner join hr.Employees E on cte.EmployeeId = E.ManagerId
)

SELECT * FROM cte 
WHERE cte.IsEnabled = 1
ORDER BY cte.IsTheBoss DESC, cte.EmployeeName

Returns a hierarchy with all the employees that depend on the specified employee

image