Recursive SQL Queries Using Common Table Expressions

by Al Beecy December 18, 2008
Given a CategoryId, this SQL snippet returns the list of parent categories.

-- CTE statement – note the parameter @CategoryId
WITH ParentCategories (CategoryId, ParentCategoryId)
AS
(
    -- Anchor member definition
    SELECT c.CategoryId, c.ParentCategoryId
    FROM Categories c
    WHERE c.CategoryId = @CategoryId
    UNION ALL
    -- Recursive member definition
    SELECT c.CategoryId, c.ParentCategoryId
    FROM Categories c
    INNER JOIN ParentCategories pc ON c.CategoryId = pc.ParentCategoryId
        AND c.ParentCategoryId IS NOT NULL
)
-- Execute the CTE
SELECT ParentCategoryId FROM ParentCategories;


For more info on CTE's, read this:
http://msdn.microsoft.com/en-us/library/ms186243.aspx

Tags: , ,

SQL Server

Comments

December 25, 2008 #

DotNetKicks.com

Trackback from DotNetKicks.com

Recursive SQL Queries Using Common Table Expressions

DotNetKicks.com

January 23, 2009 #

Web Development Community

Trackback from Web Development Community

Recursive SQL Queries Using Common Table Expressions

Web Development Community

Powered by BlogEngine.NET1.5.0.7 | Theme by Mads Kristensen