title: Use the ID of the upper select statement in the where clause of the lower select statement in a UNION
body: <p>Data:</p>
  
  <p>Table1:</p>
  
  <pre><code>ID          Value1          Date1
   1          foo             2013-04-27
   2          bar             2013-05-01
   3          umm             2013-05-29
   4          ba              2013-06-09
   5          sne             2013-04-30
   6          xyz             2013-07-11
   7          aosid           2013-07-08
  </code></pre>
  
  <p>LinkTable:</p>
  
  <pre><code>link        MainID          SubID
   A          1               3
   B          3               1
   A          1               4
   B          4               1
   A          2               6
   B          6               2
  </code></pre>
  
  <p>Query:</p>
  
  <pre><code>select t1.ID, t1.Value1, t1.Date1 
  from Table1 t1 
  where t1.Date1 between '2013-04-24' and '2013-05-08'
  
  union
  
  select t2.ID, t2.Value1, t2.Date1 
  from Table1 t2 
  where t2.ID in (select LT.SubID 
                  from LinkTable LT 
                  where LT.link = 'A' and LT.MainID = t1.ID)
  </code></pre>
  
  <p>So this is what I've just tried and I get an error that t1.ID can't be bound. This means I can't use data from the first select in the second one.</p>
  
  <p>Is there a way I can use the ID value from the first select in the second select?</p>
  
  <p>Thanks for all your help.</p>
  
  <p>Desired result:</p>
  
  <pre><code>ID        Value1        Date1
   1        foo           2013-04-27
   3        umm           2013-05-29
   4        ba            2013-06-09
   2        bar           2013-05-01
   6        xyz           2013-07-11
   5        sne           2013-04-30
  </code></pre>
  
  <p>So to explain the results a bit better the first select should include all records that are in the date range, now the second select will look to see if it is linked to one of the included records from the first select via the LinkTable.</p>