title: Recursive CTE with tree hierarchy SQL Server
body: <p>I need to use recursive with SQL Server, but i don't know how use it with my hierarchy tree.</p>
  
  <p>I need some help for creating my query and know if it's possible with CTE Recursion.</p>
  
  <p>My example : </p>
  
  <p>I have two tables : piece (piece_id) and piece_equivalence(piece1_id, piece2_id)</p>
  
  <p>First, i need to get all the piece from the first table :</p>
  
  <pre><code>SELECT DISTINCT p.record_id FROM piece p
  </code></pre>
  
  <p>Secondly, i need to check if the piece exists in the second table (piece1_id or piece2_id) </p>
  
  <pre><code>SELECT DISTINCT p.record_id           
          FROM piece p
          inner join piece_equivalence pe 
  ON (pe.piece1_id = p.record_id OR pe.piece2_id = p.record_id)
  </code></pre>
  
  <p>Thirdly, if the piece exist, I need to check the piece1_id or piece2_id. This ID can have an equivalence too.
  So I will check the second step too with my piece1_id or piece2_id.</p>
  
  <p>Currently I did recursive calling the same function with parameter piece1 or piece2.</p>
  
  <p>Graphical view with nodes :</p>
  
  <pre><code>                                                        piece_id
                                                          ___/ \___
                                                          /         \
  table : piece_equivalence               piece1_id or piece2_id    piece1_id or piece2_id
                                                  /       \              /         \
  table : piece_equivalence       piece1_id or piece2_id    same        same        same
  </code></pre>
  
  <p>Graphical with letters :</p>
  
  <pre><code>           A
         ___/ \___ ________
        /         \        \
      B            C        D
     / \          / \      / \
    D   E       F   B     E   G
   /            /
  G             H
  </code></pre>
  
  <p>A : piece
  B, C, D, E, F, G, H  are equivalences.</p>
  
  <p>WARNING : I need to stock all piece with their equivalence in a temp table. For avoiding duplicate entry or infinity loop we must check this temp table the data exists or not.</p>
  
  <p><strong>EDIT :</strong></p>
  
  <p>I did this :</p>
  
  <pre><code>WITH pieces_CTE 
  AS
  (
          SELECT TOP 1 p.record_id as parent,
                case when pe.piece1_id &lt;&gt; p.record_id then pe.piece1_id else pe.piece2_id end as enfant,
                1 as level
          FROM piece p
          inner join piece_equivalence pe ON (pe.piece1_id = p.record_id OR pe.piece2_id = p.record_id) AND pe.pertinence = 100
          AND pe.piece1_id &lt;&gt; pe.piece2_id
          UNION ALL       
          SELECT c.parent, case when enfant.piece1_id &lt;&gt; c.parent then enfant.piece1_id else enfant.piece2_id end as enfant,
          c.level+1 
          from pieces_CTE c
          INNER JOIN piece_equivalence enfant ON (enfant.piece1_id = c.parent OR enfant.piece2_id = c.parent)
          WHERE  enfant.pertinence = 100
  
  )
  
  SELECT * from pieces_CTE ORDER BY parent,level,enfant
  OPTION (MAXRECURSION 32767)
  </code></pre>
  
  <blockquote>
    <p>The statement terminated. The maximum recursion 100 has been exhausted
    before statement completion.</p>
  </blockquote>
  
  <p>But I have a large record on it, and my query has to much records, I think it's impossible to use CTE with many redundant cycles...</p>
  
  <p>But why I have the same error with TOP 1 ?</p>