title: SQL RECURSIVE TABLE INFINITE LOOP
body: <p>I have a table of companies, with three columns: holder_co_id, held_co_id, pct_owned.</p>
  
  <pre><code>holder_co_id  held_co_id  pct_owned
  A             B           25
  A             C           50
  B             C           50
  C             B           10
  </code></pre>
  
  <p>So my query identifies that A holds 25% of B, A holds 50% of C and A holds a further 5% of B through it's 50% holding in C and a further 12.5% of C through the holding in B.</p>
  
  <p>I was super super happy with myself as for once I'd nailed some sql. This did not last long. I noticed that with the mutual ownership of B and C, this sends the query into an infinite loop. I have tried stopping the loop by putting a max on the loops, but this gives ownerships in some cases still above 100%... besides, it's just not correct.</p>
  
  <p>Any ideas on how else I would do this other than:</p>
  
  <pre><code>WITH Result AS 
  (SELECT HOLDER_CO_ID, 
          HELD_CO_ID, 
          PCT_OWNED, 
          1 AS generationsremoved
  FROM dbo.ALL_HOLDING_INFO
  UNION ALL
  SELECT  P.HOLDER_CO_ID, 
          N.HELD_CO_ID, 
          P.PCT_OWNED * N.PCT_OWNED AS Expr1,
          P.generationsremoved + 1 AS Expr2
  FROM  Result AS P INNER JOIN
  dbo.ALL_HOLDING_INFO AS N ON P.HELD_CO_ID = N.HOLDER_CO_ID
  WHERE     (P.generationsremoved &lt;= 10))
  SELECT DISTINCT TOP (100) PERCENT HOLDER_CO_ID, 
                                    HELD_CO_ID, 
                                    PCT_OWNED, 
                                    generationsremoved
   FROM         Result AS Result_1
   WHERE     (PCT_OWNED &gt; 0.005)
   ORDER BY HOLDER_CO_ID, HELD_CO_ID, generationsremoved DESC
  </code></pre>