title: Sql recursive query to create a unique list
body: <p>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.<br />
  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.</p>
  
  <p>Ideally the result should be a unique list of RoleTemplate.TemplateIds.</p>
  
  <p>Table structure...</p>
  
  <pre><code>Categories
  ------------------------------
  CategoryId    uniqueidentifier
  ParentId      uniqueidentifier &lt;-- Relationship to Categories.CategoryId.
  Name          varchar (50)
  
  CategoryToRoleTemplate
  ------------------------------
  CategoryId    uniqueidentifier &lt;-- Relationship to Categories.CategoryId.
  TemplateId    uniqueidentifier &lt;-- Relationship to RoleTemplates.TemplateId.
  
  RoleTemplates
  ------------------------------
  TemplateId    uniqueidentifier
  Name          varchar (50)
  </code></pre>
  
  <p>I'm using SQL Server 2008 R2.</p>
  
  <p>Thanks!</p>
  
  <h2>EDIT:</h2>
  
  <h3>Final solution:</h3>
  
  <pre><code>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);
  </code></pre>
  
  <p>Thanks to all who answered! CTEs were the key.</p>