title: Sql Server 2014 - Deep Recursive Parent-Child Self Join
body: <p>I am trying to build a deep recursive self-join query. Having the table like:</p>
  
  <pre><code>Id | ParentId
  1  | NULL
  2  | 1
  3  | 1
  4  | 2
  5  | 3
  6  | 8
  7  | 9
  </code></pre>
  
  <p>For Id 1 my query should be fetching 1,2,3,4,5 since they are either the children of 1 or children of the children of 1. In the given example 6 and 7 should not be included in the query result.</p>
  
  <p>I tried using CTE but I am getting tons of duplicates:</p>
  
  <pre><code>WITH CTE AS (
      SELECT Id, ParentId
      FROM dbo.Table
      WHERE ParentId IS NULL
  UNION ALL
      SELECT t.Id, t.ParentId
      FROM dbo.Table t
      INNER JOIN CTE c ON t.ParentId = c.Id
  )
  SELECT * FROM CTE
  </code></pre>
  
  <p>Ideas?</p>