title: CTE recursive query loops
body: <p>I have many to many  table Dependency, that has two columns SourceId and DependsOnId. I would really like to get recursively all dependents for given Id.</p>
  
  <p>For now I have next query:</p>
  
  <pre><code>with Rec(SourceId, DependsOnId)
  as (
   select SourceId, DependsOnId from [dbo].[Dependency]
   union all
   select Rec.SourceId, Rec.DependsOnId
   from [dbo].[Dependency] d
   join [dbo].[Dependency] dd
   on d.SourceId = dd.DependsOnId and d.DependsOnId = dd.SourceId 
   join Rec 
   on Rec.DependsOnId = d.SourceId
  
  )
  SELECT * FROM Rec
  OPTION (MAXRECURSION 30000);
  </code></pre>
  
  <p>But it prefers to loop infinitely. I understand why, it is because of mirror dependencies like 1->2 and 2->1. So I need such cases to be handled only once.</p>
  
  <p>Appreciate your help.</p>