title: SQL Server skip duplicate relations (parent child) in recursion
body: <p>I have a tree, where specific node in tree can appear in another node in tree. (2 in my example):</p>
  
  <pre><code>                1
              /       \
          2               3
       /    \                 \
      4       5                   6
                                    \
                                      2
                                    /   \
                                   4     5
  </code></pre>
  
  <p>Notice 2 is duplicated. First under 1, and second under 6.
  My recursion is:</p>
  
  <pre><code>with cte (ParentId, ChildId, Field1, Field2) AS (
      select BOM.ParentId, BOM.ChildId, BOM.Field1, BOM.Field2
      from BillOfMaterials BOM 
      WHERE ParentId=x
  
      UNION ALL
  
      SELECT BOM.ParentId, BOM.ChildId, BOM.Field1, BOM.Field2 FROM BillOfMaterials BOM 
      JOIN cte on BOM.ParentId = cte.ChildId
  )
  select * from cte;
  </code></pre>
  
  <p>But the problem is that in result relation 2-4 and 2-5 is duplicated (first from relation 1-2 and second from relation 6-2):</p>
  
  <pre><code>ParentId    ChildId                 OtherFields
      1           2
      1           3
      2           4  /*from 1-2*/
      2           5  /*from 1-2*/
      3           6
      6           2
      2           4  /*from 6-2*/
      2           5  /*from 6-2*/
  </code></pre>
  
  <p>Is there any way, to skip visiting duplicated relationships? I do no see any logic why should recursion run over rows that are already in result. It would be faster. Something like that:</p>
  
  <pre><code>        with cte (ParentId, ChildId, Field1, Field2) AS (
              select BOM.ParentId, BOM.ChildId, BOM.Field1, BOM.Field2
              from BillOfMaterials BOM 
              WHERE ParentId=x
  
              UNION ALL
  
              SELECT BOM.ParentId, BOM.ChildId, BOM.Field1, BOM.Field2 FROM BillOfMaterials BOM 
              JOIN cte on BOM.ParentId = cte.ChildId
  ------&gt;     WHERE (select count(*) FROM SoFarCollectedResult WHERE ParentId=BOM.ParentId AND ChildId=BOM.ChildId ) = 0
          )
          select * from cte;
  </code></pre>
  
  <p>I found <a href="https://stackoverflow.com/questions/829514/prevent-recursive-cte-visiting-nodes-multiple-times">this thread</a>, but it is 8 years old.<br>
  I am using SQL server 2016.</p>
  
  <p>If this is not possible, then my question is how can I remove duplicates from final result, but check distinct only on ParentId and ChildId columns?</p>
  
  <p><strong>Edited:</strong></p>
  
  <p>Expected result is:</p>
  
  <pre><code>ParentId    ChildId                 OtherFields
      1           2
      1           3
      2           4
      2           5
      3           6
      6           2
  </code></pre>