Recursive SQL using CTE in SQL Server 2005

How to do a recursive query in SQL Server 2005, Recursive in the terms of you got a table where it has foreign key to the other record on the same table. Assume a table employee where a record on that table can be an employee or a manager which is an employee as well, or let’s call it when you have nested structure within one table, call it as a tree structure. I’ve googled and found the way in doing it so I believe it’s worthy for me to share with everyone

Table Schema

How to get the whole structure by using one SQL query, we can utilize CTE(Common table expression) and using UNION ALL to do the recursive and do the INNER JOIN with the CTE it self

--get all contentPageID for the page and its children
WITH dynamicPages(contentPage_id, parentContent_id, contentPageName) AS
			c.contentPage_id, c.parentContent_id, c.ContentPageName
			tblContentPage c
			c.contentPage_id = @contentPage_id
			c.contentPage_id, c.parentContent_id, c.contentPageName
			tblContentPage c
			dynamicPages b
			c.parentContent_id = b.contentPage_id
SELECT * FROM dynamicPages

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s