Hierarchical queries with Common Table Expressions

time to read 4 min | 762 words

[Via Mitch Denny's Blog ] [Via Sql Down Under]

It's usualy hard to efficently get hierarchical data using SQL. But apperantely SQL Server can do that easily. The key to this is to create common table expression that recursively points to itself, like this:

WITH AllEmployeesOf([EmployeeId], [Name], [ManagerName])



      SELECT m.[EmployeeId], m.[Name], convert(nvarchar(50),null) as ManagerName

            FROM [Employees] m WHERE @employee_id = m.[EmployeeId]


      SELECT e.[EmployeeId], e.[Name], AllEmployeesOf.[Name] as ManagerName

            FROM [Employees] e JOIN AllEmployeesOf ON

      e.[ManagerId] = AllEmployeesOf.[EmployeeId]


SELECT * FROM AllEmployeesOf

Before this I would resort to either getting all of the rows or issuing several queries. Nice.