title: SQL CTE for looping case
body: <p>I am having Membership table with below structure</p>
  
  <pre><code>GroupID  MemberType MemberID
  
     G1     user       U1
     G1     group      G2
     G2     user       U2
     G2     group      G3
     G3     group      G1
  </code></pre>
  
  <p>Here, my need is.. need to get Group Members by using GroupID..</p>
  
  <p>For <strong>Direct Members</strong>, we can easily do it by using GroupID column in where query..</p>
  
  <p>but to get also <strong>Nested Members</strong>, we need to execute this query recursively. For that I have used the following CTE Query.</p>
  
  <pre><code>;with cte as 
  (
      select *, groupid as mastergroup,1 as level from mytable 
      union all       
      select t1.memberid, t1.membertype, null, cte.mastergroup, level+1
      from cte 
          inner join mytable t1
              on t1.groupid=cte.memberid
      where cte.membertype='group'
  ) 
  select * from cte
  where mastergroup='G1'
  </code></pre>
  
  <p>Nice. it is working fine if there is no looping between the nested groups. but In my case</p>
  
  <p>I am having loop structure in this way...G1-->G2-->G3-->G1... so in this case, my query is running indefinitely in CTE looping.</p>
  
  <p>Can any help me to resolve this case?.....Thanks in advance.....</p>