title: Left joining and subqueries with multiple tables
body: <p>Here is my draft code which will fail based on placement, location of parenthesis and assigning a name to each of the tables.  I am unsure of how to create a nested query with a left join when joining on multiple tables.  </p>
  
  <p>Any suggestions for how to rewrite the below code is greatly appreciated.</p>
  
  <p>Thanks in advance for your help!</p>
  
  <pre><code>IF OBJECT_ID('tempdb..#1') IS NOT NULL DROP TABLE #1 ;  
  IF OBJECT_ID('tempdb..#2') IS NOT NULL DROP TABLE #2 ;  
  IF OBJECT_ID('tempdb..#3') IS NOT NULL DROP TABLE #3 ;  
  
  
  SELECT *  FROM   nwh_analytics.dbo.km_cmr_table a 
  
  left join (
  select  
     b.prov_id + b.prov_site_num as prov,
     count(distinct b.claim_num) total_claims_billed,
     sum(b.paid) as total_dollars_paid
  
  into    
  #1 -- drop table #1 
  
  from    
  datawarehouse.dbo.claims b
  where   
  dos &gt;= '2017-01-01'
  and fee_sched like 'tup%'
  and a.code = b.CPT_CODE
  
  group by    
     prov_id + prov_site_num)
  
     on a.code = b.CPT_CODE
  
  
  Select * From nwh_analytics.dbo.km_cmr_table a 
  
  left join(
  select  
  d.contract_id,
  d.contract_legal_name,
  b.prov_id + b.prov_site_num as prov,
  c.prov_full_name,
  count(distinct b.claim_num) as claims_with_new_codes,
  sum(b.paid) as paid_for_claims_with_new_codes,
  e.total_claims_billed,
  e.total_dollars_paid
  
  into    
  #2
  
  from    
  datawarehouse.dbo.claims b 
  join datawarehouse.dbo.providers c
  on b.prov_id = c.prov_id
  join datawarehouse.dbo.providers_site d
  on b.prov_id + b.prov_site_num =c.prov_id + d.site_num
  join #1 e
  on b.prov_id + b.prov_site_num = e.prov
  
  where   
  b.dos &gt;= '2017-01-01'
  and b.fee_sched like 'tup%')
  a.code = b. FEE_CPT
  
  
  
  Select * From nwh_analytics.dbo.km_cmr_table a 
  
  left join(select    
  c.contract_id,
  c.contract_legal_name,
  a.prov_id + a.prov_site_num as prov,
  b.prov_full_name,
  a.claim_num,
  a.line_num,
  a.dos,
  a.cpt_code,
  cd.cpt_desc,
  a.proc_mod1,
  a.proc_mod2,
  a.billed,
  a.allowed,
  a.paid,
  a.deny_code,
  a.paid_date,
  a.Fee_Sched
  
  into    
  #3
  
  from    
      datawarehouse.dbo.claims a 
      join datawarehouse.dbo.providers b
      on a.prov_id = b.prov_id
      join datawarehouse.dbo.providers_site c
      on a.prov_id + a.prov_site_num = c.prov_id + c.site_num
      join datawarehouse.dbo.cpt_codes cd
      on a.cpt_code = cd.cpt_cd
  
    where 
     a.dos &gt;= '2017-01-01'
     and a.fee_sched like 'tup%')
     on a.Code = b. Fee_CPT
  </code></pre>