title: Why am I getting an error when trying to join tables in my SQL Server query
body: <p>I have written the query below to gather data relating to truck driver revenue.  Both the main query and the joined query work properly. However, when I combine them, I get an error </p>
  
  <blockquote>
    <p>Msg 4104, Level 16, State 1, Line 37<br>
    The multi-part identifier "tn.ctripnumber" could not be bound</p>
  </blockquote>
  
  <p>relating to <code>tRev.ctripnumber = tn.ctripnumber</code>. I have tried using the revenue table name as well as the tRev alias.</p>
  
  <pre><code>select 
      tn.ctripnumber as "Load Number",
      tr.cresourcedesc as "Carrier/Driver",
      tr.resourcetype as "Resource Type",
      tn.cfirmorigin as "Pickup Origin",
      tn.corigaddress as "Origin Address",
      tn.corigcity as "Origin City",
      tn.corigstate as "Origin State",
      tn.corigzip as "Origin Zip",
      tn.dorigappt_start as "Scheduled Arrival Date",
      tn.dorigappt_end as "Scheduled Late Arrival Date",
      tn.dtripstartdate as "Actual Arrival Date",
      datediff(minute,tn.dorigappt_start,tn.dtripstartdate) as "Arrival Diff",
      (case when tn.dtripstartdate-tn.dorigappt_start &lt; 0 then 'Early' when tn.dorigappt_start-tn.dtripstartdate = 0 then 'On-time' else 'Late' end) as Arrival_Rank,
      tn.cfirmdestination as "Delivery Destination",
      tn.cdestaddress as "Destination Address",
      tn.cdestcity as "Destination City",
      tn.cdeststate as "Destination State",
      tn.cdestzip as "Destination Zip",
      tn.ddestappt_start as "Scheduled Delivery Date",
      tn.ddestappt_end as "Scheduled Late Delivery Date",
      tn.ddeliverydate as "Actual Delivery Date",
      datediff(minute,tn.ddestappt_start,tn.ddeliverydate) as "Delivery Diff",
      (case when tn.ddeliverydate-tn.ddestappt_start &lt; 0 then 'Early' when tn.ddeliverydate-tn.ddestappt_start = 0 then 'On-time' else 'Late' end) as "Delivery Rank",
      tn.nideadheadmiles as "Deadhead Miles",
      tn.niloadedmiles as "Loaded Miles",
      tn.nideadheadmiles + tn.niloadedmiles as "Total Miles"
  from 
      tripnumber tn,tripresources tr
  inner join
      (select  
           r.ctripnumber, sum(Revenue_Subtotal) as "Revenue" 
       from 
           (select 
                r.ctripnumber, r.cmethod, sum(r.curevenue) as Revenue_Subtotal
            from 
                revenuedtl r 
            where 
                r.cmethod in (select distinct r.cmethod from revenuedtl r)
            group by 
                r.ctripnumber, r.cmethod) r
       --where r.cmethod like 'BROK%'
       group by 
           r.ctripnumber) tRev on tRev.ctripnumber = tn.ctripnumber
  where 
      tn.ctripnumber = tr.ctripnumber
      --and tn.ctripnumber = '324412'
      and tr.resourcetype in ('D','M')
      and tn.dtripstartdate &gt;= '2018-12-01 00:00:00.000'
      and tn.dtripstartdate &lt; '2019-01-01 00:00:00.000'
  </code></pre>