title: sql server how to retrieve a full tree given any child?
body: <p>I have a table with id and parent_id. I have seen many CTE on how to generate the tree. However I can't seem to filter the tree in order to retrieve a full tree given any node from it.</p>
  
  <p>Given the values</p>
  
  <pre><code>1, NULL
  2, 1
  3, 2
  4, 2
  5, NULL
  6, 5
  7, NULL
  </code></pre>
  
  <p>If I filter by id any of the values 1,2,3 or 4 I should get the tree</p>
  
  <pre><code>1, NULL
  2, 1
  3, 2
  4, 2
  </code></pre>
  
  <p>for 5 or 6</p>
  
  <pre><code>5, NULL
  6, 5
  </code></pre>
  
  <p>for 7</p>
  
  <pre><code>7, NULL
  </code></pre>
  
  <p>Can this be achieved using CTE?</p>