Sql recursive query to create a unique list

Question

Hi,

I need to move some code from C# into a Stored Procedure for speed reasons. What I'm trying to get is a unique list of TemplateIds from the RoleTemplates (or CategoryToRoleTemplate) table based on a CategoryId.
However, I need the query to walk the Category.ParentId relationship, and collection all of the parent's related TemplateIds. This needs to happen until the ParentId is null.

Ideally the result should be a unique list of RoleTemplate.TemplateIds.

Table structure...

Categories
------------------------------
CategoryId    uniqueidentifier
ParentId      uniqueidentifier <-- Relationship to Categories.CategoryId.
Name          varchar (50)

CategoryToRoleTemplate
------------------------------
CategoryId    uniqueidentifier <-- Relationship to Categories.CategoryId.
TemplateId    uniqueidentifier <-- Relationship to RoleTemplates.TemplateId.

RoleTemplates
------------------------------
TemplateId    uniqueidentifier
Name          varchar (50)

I'm using SQL Server 2008 R2.

Thanks!

EDIT:

Final solution:

with CategoryHierarchy (ParentId)
as (
    -- Anchor member definition
    select CategoryId from Categories
    where CategoryId = @id
    union all

    -- Recursive member definition
    (select c.ParentId from Categories as c
        inner join CategoryHierarchy as p
        on c.CategoryId = p.ParentId)
)

select distinct TemplateId from CategoryToRoleTemplates where CategoryId in (select CategoryId from CategoryHierarchy);

Thanks to all who answered! CTEs were the key.

Answer 1

Use a recursive common table expression:

http://msdn.microsoft.com/en-us/library/ms186243.aspx

Answer 2

Hi,

Please check this link http://msdn.microsoft.com/en-us/library/ms186243.aspx

I would go first with the table Categories with the with syntax and after that join with the others tables.

Answer 3

I'm short on time at the moment, so I can't be specific, but I would look into Common Table Expressions, which I've used successfully in the past to implement recursion.

Answer 4

I would suggest a CTE for doing that query. Keep in mind though that the tree will actually START at null and go until exhausted.

Example (may or may not work OOB given your code):

; WITH CategoryTree(CategoryID, sorthelp) AS (SELECT CategoryID, 0 FROM Categories WHERE ParentID IS NULL)

UNION ALL

(SELECT C.CategoryID, CT.sorthelp + 1 FROM Categories C INNER JOIN CategoryTree CT ON C.PARENTID = CT.CategoryID)

SELECT DISTINCT TemplateID FROM RoleTemplates WHERE CategoryID IN (SELECT CategoryID FROM CategoryTree)

Good Point(tm): Don't forget the semicolon before the WITH keyword.