KARPACH

WEB DEVELOPER BLOG

MS SQL 2005 Recursive Query Example

Let’s take as an example the following table:

cmsPage

PageId ParrentPageId
6 NULL
44 6
50 44
51 44
52 6

Let’s find all children of a page with PageId 44 using a common table expression:

WITH PagesList (PageId)
AS
(
    -- Anchor member definition
    SELECT PageId FROM cmsPage WHERE PageId=44
    UNION ALL 
    -- Recursive member definition
    SELECT c.PageId From cmsPage as c
    INNER JOIN PagesList as p
    ON c.ParrentPageId = p.PageId
)
 
-- Statement that executes the CTE
SELECT PageId FROM PagesList
GO

Result would be:

PageId
44
50
51
Posted on March 10, 2008 by

Comments

Posted on 10/6/2008 10:31:15 AM by Vamsi

Thanks very much.. was very helpful

Posted on 7/13/2009 09:26:24 AM by Undi

Nice query here. It helped me so much

Posted on 8/19/2009 12:55:12 AM by Carl

Hi, Is there a way to sort this list? i.e. add a SortId column and then have each nested set of children ordered correctly etc… As I have found it very difficult to do this efficiently!

Hmm, I didn’t get why is this difficult? You can modify last statement as follow:

SELECT PageId FROM PagesList ORDER BY PageId