title: MS SQL Server: View with all parents of a child
body: <p>I have the following mapping table which contains pairs of parent/child ids:</p>
  
  <pre><code>parent_id | child_id
     3      |     5
     5      |     4
     4      |     9
     6      |     7
     7      |     8
  </code></pre>
  
  <p>I need to create a view for this table which would list all parents of a given child. Using the example above, the resulting view should look something like this:</p>
  
  <pre><code>parent_id | child_id
     3      |     5
     3      |     4
     5      |     4
     3      |     9
     5      |     9
     4      |     9
     6      |     7
     6      |     8
     7      |     8
  </code></pre>
  
  <p>The view would have a row for each parent (direct or indirect) of a certain id, the more parents, the more rows. One could assume that there are no loop in this table.</p>
  
  <p>I'm not sure how to approach this as I've looked into recursive selects and loops but I'm unsure how to incorporate them into a view.</p>