title: LEFT JOIN on Inline Tables
body: <p>Google hasn't helped me here nor has Microsoft online help yet.</p>
  
  <p>I have Inline tables which are generated as a subquery. (See simplified code below, I have several more inline tables.)</p>
  
  <p>Now, this is fine where I have data. However, there are cases where I need to return results when there isn't data. For example, Inline table 1 returns my number of active customers... If I specifiy a range where there are no active customers I get no results for the whole query.</p>
  
  <p>This is due to my join (AND IL1.transaction_id = th.transaction_id)</p>
  
  <p>How can I left join the inline table?</p>
  
  <p>I tried LEFT JOIN IL1 on il1.transaction_id = th.transaction_id but it says the table doesn't exist.</p>
  
  <pre><code>select  SUM(th.total_net_retail_central) as 'Net Purchases  TY', 
              IL1.Active as 'Number of Active Customers TY',
              COUNT(th.transaction_id) as 'Number of Transactions TY'
  
  FROM        
  
  (SELECT transaction_type, COUNT(DISTINCT customer_id) as 'Active' from transaction_header  
  where transaction_date BETWEEN @Active and @ToDate group by transaction_type)IL1, 
  transaction_header th
  
  INNER JOIN transaction_type tt ON th.transaction_type = tt.transaction_type
  WHERE 
  th.transaction_date Between @FromDate AND @ToDate
  
  AND         IL1.transaction_type = th.transaction_type
  
  GROUP BY 
              tt.transaction_type_description, IL1.Active
  </code></pre>
  
  <p>Any help is really appreciated.</p>