Recursive CTE query

Reference: Programming Microsoft® SQL Server™ 2005 By Andrew J. Brust, Stephen Forte

Today I’ve gone thru the interesting recursive concepts of CTE from the book which is mentioned just above. I just want to share that concept with you all.

We will come to know the true power of CTEs, when we use them recursively to perform hierarchical queries on tree-structured data. A recursive CTE is constructed with two queries. The first, the anchor member (AM), is a non-recursive query; the second, the recursive member (RM), is the recursive query. Within your CTE's parentheses (after the AS clause), you define queries that are independent or refer back to the same CTE. The AM and RM are separated by a UNION ALL statement. AMs are invoked only once; RMs are invoked repeatedly until the query returns no rows. You can append multiple AMs to each other using a UNION or UNION ALL operator, depending on whether you want to eliminate duplicates. (You must append recursive members using a UNION ALL operator.) Here is the syntax:

WITH SimpleRecursive(field names)

AS

(

<Select Statement for the Anchor Member>

UNION ALL

<Select Statement for the Recursive Member>

)

SELECT * FROM SimpleRecursive

The following example demonstrates this feature. We'll create a table of employees and a self-referencing field back to Employee_ID called ReportsTo. We'll then write a query that returns all the employees who report to Stephen (Employee_ID=2) and all the employees who report to Stephen's subordinates.

--create a table with tree data

CREATE TABLE Employee_Tree (Employee_NM nvarchar(50), Employee_ID int PRIMARY KEY, Repor

tsTo int)

--insert some data, build a reporting tree

INSERT INTO Employee_Tree VALUES('Richard', 1, NULL)

INSERT INTO Employee_Tree VALUES('Stephen', 2, 1)

INSERT INTO Employee_Tree VALUES('Clemens', 3, 2)

INSERT INTO Employee_Tree VALUES('Malek', 4, 2)

INSERT INTO Employee_Tree VALUES('Goksin', 5, 4)

INSERT INTO Employee_Tree VALUES('Kimberly', 6, 1)

INSERT INTO Employee_Tree VALUES('Ramesh', 7, 5)

Our table looks like this:

Employee_NM Employee_ID ReportsTo

-------------------------------------------------------------------------------------

Richard 1 NULL

Stephen 2 1

Clemens 3 2

Malek 4 2

Goksin 5 4

Kimberly 6 1

Ramesh 7 5

Here's the recursive query to determine which employees report to Stephen:

--Recursive Query

WITH SimpleRecursive(Employee_NM, Employee_ID, ReportsTo)

AS

(SELECT Employee_NM, Employee_ID, ReportsTo

FROM Employee_Tree WHERE Employee_ID = 2

UNION ALL

SELECT p.Employee_NM, p.Employee_ID, p.ReportsTo

FROM Employee_Tree p INNER JOIN

SimpleRecursive A ON A.Employee_ID = P.ReportsTo

)

SELECT sr.Employee_NM AS Employee, et.Employee_NM AS Boss

FROM SimpleRecursive sr INNER JOIN Employee_Tree et

ON sr.ReportsTo = et.Employee_ID

Here are the results:

Employee       Boss

Stephen        Richard

Clemens        Stephen

Malek          Stephen

Goksin         Malek

Ramesh         Goskin

This recursion starts where Employee_ID = 2 (the Anchor Member or the first SELECT). It picks up that record and then, using the Recursive Member (the SELECT after the UNION ALL), picks up all the records that report to Stephen and that record's children. (Goksin reports to Malek, and Malek reports to Stephen.) Each subsequent recursion tries to find more children that have as parents the employees found by the previous recursion. Eventually the recursion returns no results, and that is what causes the recursion to stop (the reason why Kimberly is not returned). If the anchor member is changed to Employee_ID = 1, then Kimberly will also be returned in the results.