Referring to a column from a different table in inner join select

Question

I'm trying to join onto an inner query, which is having it's results filtered by a value in the other table, so I can select the top result and use a value in it multiple times in my main select statement, but I get the error as below:

The multi-part identifier "TessSCCall.Call_Num" could not be bound.

See below for the code so far:

SELECT BestAppointmentOffer AS foo -- I'm using this lots of times in different fields
        BestAppointmentOffer AS bar -- I'm using this lots of times in different fields
    ....
FROM TessSCEmploy 
INNER JOIN TessSCCall on TessSCEmploy.Employ_Num = TessSCCall.Call_Employ_Num
INNER JOIN
(
   SELECT TOP 1 dbo.Aqua_Midnight(AppointmentStartTime) 
     AS BestAppointmentOffer,    CallNumber
   FROM AQWEB.[360Tracking].dbo.AppointmentOffers
   WHERE CallNumber = TessSCCall.Call_Num
   ORDER BY AppointmentStartTime) AS Appointment 
 on TessSCCall.Call_Num = Appointment.CallNumber
where ....

How can I get this to work, so I can use the value from my query (that I'm currently trying to join) in calculations in multiple fields, without repeating it?

Answer 1

The easiest way to do this would be to use CROSS APPLY but since you are using SQL Server 2000 you don't have that option. You should be able to use an aggregate function to get the top result for each appointment time:

select BestAppointmentOffer AS foo -- I'm using this lots of times in different fields
        BestAppointmentOffer AS bar -- I'm using this lots of times in different fields
    ....
from TessSCEmploy 
inner join TessSCCall 
  on TessSCEmploy.Employ_Num = TessSCCall.Call_Employ_Num
INNER JOIN 
(
  SELECT min(dbo.Aqua_Midnight(AppointmentStartTime)) AS BestAppointmentOffer, 
    CallNumber
  FROM AQWEB.[360Tracking].dbo.AppointmentOffers
  GROUP BY CallNumber
) AS Appointment 
  on TessSCCall.Call_Num = Appointment.CallNumber
where ....