title: Writing a recursive SQL query on a self-referencing table
body: <p>I have a database with a table called Items, that contains these columns:</p>
  
  <ul>
  <li>ID - primary key, uniqueidentifier  </li>
  <li>Name - nvarchar(256) </li>
  <li>ParentID - uniqueidentifier</li>
  </ul>
  
  <p>The name field can be used to build out a path to the item, by iterating through each ParentId until it equals '11111111-1111-1111-1111-111111111111', which is a root item.</p>
  
  <p>So if you had a table that had rows like</p>
  
  <pre><code>ID                                   Name        ParentID
  -------------------------------------------------------------------------------------
  11111111-1111-1111-1111-111111111112 grandparent 11111111-1111-1111-1111-111111111111
  22222222-2222-2222-2222-222222222222 parent      11111111-1111-1111-1111-111111111112
  33333333-3333-3333-3333-333333333333 widget      22222222-2222-2222-2222-222222222222
  </code></pre>
  
  <p>So if I looked up an item with id '33333333-3333-3333-3333-333333333333' in the example above, i'd want the path</p>
  
  <pre><code>/grandparent/parent/widget 
  </code></pre>
  
  <p>returned. i've attempted to write a CTE, as it looks like that's how you'd normally accomplish something like this - but as I don't do very much SQL, I can't quite figure out where i'm going wrong. I've looked at some examples, and this is as close as I seem to be able to get - which only returns the child row.</p>
  
  <pre><code>declare @id uniqueidentifier
  set @id = '10071886-A354-4BE6-B55C-E5DBCF633FE6'
  ;with ItemPath as (
      select a.[Id], a.[Name], a.ParentID 
          from Items a
              where Id = @id
  
      union all
  
      select parent.[Id], parent.[Name], parent.ParentID 
          from Items parent 
              inner join ItemPath as a
                  on a.Id = parent.id
                      where parent.ParentId = a.[Id]
  )
  select * from ItemPath
  </code></pre>
  
  <p>I have no idea how i'd declare a local variable for the path and keep appending to it in the recursive query. i was going to try to at least get all the rows to the parent before going after that. if anyone could help with that as well - i'd appreciate it.</p>