Join to subquery not working?

Question

I should be able to reference tables in the join clauses from a subquery, shouldn't I?

But the following query is giving me errors saying they can't be bound:

select *
from call c
JOIN call_task ct ON c.call_no=ct.call_no AND ct.calltask_no = (select min(ict.calltask_no) FROM call_task ict WHERE ict.call_no=c.call_no)
JOIN business b ON c.service_business_id=b.business_id
JOIN item i ON ct.item_id=i.item_id
JOIN    ( select top 1 * 
      FROM contract_line icl
      WHERE icl.item_id = i.item_id 
       AND icl.location_no = c.service_location_no 
       AND icl.business_id = b.business_id
      ORDER BY icl.cancel_date asc
     ) cl ON i.item_id=cl.item_id 
       AND cl.location_no=c.service_location_no 
       AND cl.business_id=b.business_id
JOIN [contract] co ON cl.cont_no=co.cont_no
JOIN business_location bl ON bl.business_id=c.service_business_id AND bl.location_no=c.service_location_no
WHERE b.bus_code='INGRAM04'
AND ct.cont_no is null
AND call_sts NOT IN ('BB', 'BI', 'CA', 'CL', 'IP')
--AND cl.end_date > c.entry_date
ORDER BY c.create_time

The errors are: Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "i.item_id" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "c.service_location_no" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "b.business_id" could not be bound.

I don't understand why I'm getting these errors, and can't think of any way around it. The item_no on a contract_line can appear more than once on a contract, if it was canceled and a new line created for instance. In those cases I need to ignore the cancelled line and pull the current one. Doing the subquery and ordering by cancel_date pulls the nulls first so it accomplishes what I want... but this weird binding error is screwing me up. I know I've used this technique before so now I'm confused...

Answer 1

The error I believe is coming from the select you are aliasing with cl then trying to join on

WHERE   icl.item_id = i.item_id 
AND icl.location_no = c.service_location_no 
AND icl.business_id = b.business_id

you can't reference "i" and "c" and "b" there.

Answer 2

You can't use dependent sub-queries in JOIN. Use APPLY instead. Using KM's example:

declare @table table (t int);
select     t1.t     
from @table t1    
cross apply (select t2.t from @table t2 where t1.t=t2.t) as dt

Answer 3

that is not a sub query, but a derived table (or some call it an inline view). here is a simple query to show the same problem:

declare @table table (t int)
select 
    t1.t 
    from @table t1
    inner join (select t2.t from @table t2 where t1.t=t2.t) dt on 1=1

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "t1.t" could not be bound.

everything between the ( ) is isolated from the outer query.

this is a subquery that works:

select 
    t1.t 
    from @table t1
    WHERE exists (select * from @Table t2 where t1.t=t2.t)

OUTPUT:

t
-----------

(0 row(s) affected)