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
Subscribe