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?

Taryn · Accepted Answer · 2013-05-16T11:11:31.757

2

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 ....

edited May 16 '13 at 11:11

answered May 16 '13 at 11:05

Taryn

242,637
56
362
405

Had to change MAX to MIN, but works fine now, thanks. (Possibly my fault as I might have had order by round the wrong way). – George Duckett May 16 '13 at 11:10
@GeorgeDuckett It should be `min`, I assumed the order by was desc. My answer has been updated. – Taryn May 16 '13 at 11:12

score 0 · Answer 2 · answered May 16 '13 at 11:04

0

You need to CROSS APPLY to correlate columns in a derived table

...

TessSCEmploy inner join TessSCCall on TessSCEmploy.Employ_Num = TessSCCall.Call_Employ_Num

CROSS APPLY
(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 ....

CROSS APPLY is the correct construct anyway for what you are doing, which is a "TOP 1 per Something"

answered May 16 '13 at 11:04

gbn

422,506
82
585
676

its tagged SQL Server 2000, does it have `CROSS APPLY`? – Taryn May 16 '13 at 11:05
@bluefeet: I never consider that these days. Shouldn't be allowed – gbn May 16 '13 at 11:06

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

2 Answers2