title: Is CTE the correct way to handle recursion with two tables?
body: <p>I have a query setup below, and I'm having trouble with the recursion piece. I start with a contract(s), Abc and Xyz from Table1. I take Table1.Id, groupNo and look them up in Table2, to get the contracts(s) there, then look those contracts back up in Table1, repeating the process until it eventually returns null, and capturing the last iteration.  Is CTE the way to handle this? If so, could someone help with the last iteration. I tried nesting, and haven't got it to work.</p>
  
  <h2>Table Structure</h2>
  
  <pre><code>create table Table1 (id int, groupNo int, contract varchar(3))
  insert into Table1 values(33,2,'Abc')
  insert into Table1 values(34,8,'Xyz')
  insert into Table1 values(88,11,'123')
  insert into Table1 values(89,11,'456')
  
  create table Table2 (id int, groupNo int, contract varchar(3))
  insert into Table2 values(34,8,'123')
  insert into Table2 values(34,8,'456')
  insert into Table2 values(89,11,'789')
  </code></pre>
  
  <h2>Query</h2>
  
  <pre><code>with myCTE (id,groupNo,contract) as
  (
      select 
          t1.id
          ,t1.groupNo
          ,t2.contract 
      from Table1 t1
          inner join Table2 t2 on t1.id = t2.id and t1.groupNo = t2.groupNo
      union all
      select
          t1.id
          ,t1.groupNo
          ,c2.contract
      from myCTE c2
          inner join Table1 t1 on c2.contract = t1.contract
  )
  
  select top 10 id, groupNo, contract
  from myCTE
  </code></pre>
  
  <h2><a href="http://sqlfiddle.com/#!3/edfa9/3/0" rel="nofollow"><code>SQL FIDDLE</code></a></h2>