Recursive SQL statment using CTE "With as"
having such a table in a MS SQL Server 2005
where the table represents some employees and their tree structure in the organization, where each employee has a foreign key "ManagerID" which is the ID of another Employee in the same table, and this table is represented with the following tree
the problem is that i want to select the employees with an additional field holding their level in the previous tree a recursive solution, huh !! ok, that can be solved using recursive SQL function but here is another approach...
just SQL select statement that uses CTE "Common Table Expressions" introduced in MS SQL Server 2005 -yeah i know i'm too late :( - so here is the SQL Select Query
WITH t AS ( --select the root column(s) --The anchor member SELECT Employee.EmpID, Employee.ManagerID, Employee.Name, 0 AS LEVEL FROM Employee WHERE ManagerID IS NULL UNION ALL --select the children recursively --The recursive member SELECT Employee.EmpID, Employee.ManagerID, Employee.Name, LEVEL + 1 AS LEVEL FROM Employee JOIN t ON t .EmpID = Employee.ManagerID ) SELECT * FROM t
the term "With t as" is the CTE which allows us to create a table on the fly named t, where this table is filled using the query between the preceding brackets.
as any normal recursion, there must be a starting point and a terminating condition the starting point here is the Anchor Member which is the first select query that gets the Employees whose "ManagerID" is NULL "they are the managers" then the statement "Union All" that applies the next query over and over till it returns nothing "which is the terminating condition here" and the recursive member here uses the selected rows in the table "t" till the moment to join on the ManagerID in the Employee table with the ID of the already selected Managers in t in the last execution of the recursive member running such query on the table "Employee" will produce the following result set
i hope that helps... and here is my reference