title: Find all parents of given ids in tree in SQL Server
body: <p>I have a tree structure in single table and item relationships in another table. I need to find out all the parents from the given items.</p>
  
  <pre><code>SELECT Id FROM dbo.Items WHERE Selected = 1
  </code></pre>
  
  <p>Since I get 6,9, 11 from that query, I would need to return parent item ids of those which are 7, 5, 2. </p>
  
  <p><a href="https://i.stack.imgur.com/GDpkl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GDpkl.png" alt="enter image description here"></a></p>
  
  <p>I think I should use some kind of recursive CTE for this, but I'm not sure where to start.</p>
  
  <p>Could you help me out? Thanks!</p>
  
  <pre><code>dbo.Relationship
  Id       ParentId
  3           6
  6           7
  8           7
  7           2
  4           9
  9           5
  5           2   
  
  
  dbo.Items
  Id              Selected
  2                   0
  3                   0
  4                   0
  5                   0
  6                   1
  7                   0
  8                   0
  9                   1
  11                  1
  </code></pre>