Sub Query in Join does not recognise outer query alias

Question

Sub Query in Join does not recognise outer query alias

David Baldwin

SSC Enthusiast

Points: 161
More actions
May 25, 2004 at 9:10 am

#108190

The Following gives theses errors in query analyzer (or anything I run the SQL in)
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'x' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'y' does not match with a table name or alias name used in the query.
tbl_sites is aliassed as x and tbl_History as y
SELECT uu.History_Duty_ID, uu.Day_Date, uu.Employee_No,
                      dbo.tbl_Employees.Surname + N' ' + dbo.tbl_Employees.Initials AS Employee_Name, y.Site_Ref, x.Company,
                      x.Town, x.Company + N' ' + x.Town AS Company_Details, uu.DOW,
                      uu.Expected_Start, uu.Expected_Stop, uu.Expected_Break,
                      uu.Expected_travel, uu.Activity, uu.Status, uu.Actual_Start,
                      uu.Actual_Stop, uu.Actual_Break, uu.Actual_Travel, uu.Verified_By,
                      uu.Verification_Date, uu.Call_Logged_Or_Signed, uu.History_Rec_ID,
                      y.Start_of_Week, x.Country, x.Area, x.Region, uu.Verified,
                      y.Log_ID,dd.effective_From
FROM   dbo.tbl_History_Duties uu LEFT OUTER JOIN
                      dbo.tbl_Employees ON uu.Employee_No = dbo.tbl_Employees.Employee_No LEFT OUTER JOIN
                      dbo.tbl_History as y ON uu.History_Rec_ID = y.History_Rec_ID LEFT OUTER JOIN
                      dbo.tbl_Sites as x ON y.Site_Ref = x.Site_Ref

LEFT OUTER JOIN
(select top 1 * from
tbl_Site_parameters z
where z.site_ref = x.Site_Ref and z.effective_from <= y.Start_of_Week
order by z.effective_from desc ) dd ON dd.site_ref = x.Site_Ref

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ThomasH SSCertifiable Points: 7437 More actions · Answer 1

You are accessing the data from tbl_Site_parameters table within a "derived" table structure. Within this structure you can not access values from outside the structure.

Try JOINing the tbl_Site_parameters table without using a derived table.

Once you understand the BITs, all the pieces come together

David Baldwin SSC Enthusiast Points: 161 More actions · Answer 2

Unfortunately I cannot join the table in any other way, I will use VB instead, however I did not know why it was not working and you have saved me a lot of time thanks!

ThomasH SSCertifiable Points: 7437 More actions · Answer 3

David, I do not know your data, and can not know what the "best" method will be for you, but have you considered putting the data from the tbl_Site_parameters table into a #Temp table (maybe with indecies on the #Temp table), prior to your SELECT, then JOINing on the #Temp table within the SELECT?

Once you understand the BITs, all the pieces come together

Peter P SSCrazy Points: 2777 More actions · Answer 4

you should be able to remove the WHERE clause from your derived table and use it in your JOIN. You are already partially doing this. Try this:

SELECT uu.History_Duty_ID, uu.Day_Date, uu.Employee_No,

dbo.tbl_Employees.Surname + N' ' + dbo.tbl_Employees.Initials AS Employee_Name, y.Site_Ref, x.Company,

x.Town, x.Company + N' ' + x.Town AS Company_Details, uu.DOW,

uu.Expected_Start, uu.Expected_Stop, uu.Expected_Break,

uu.Expected_travel, uu.Activity, uu.Status, uu.Actual_Start,

uu.Actual_Stop, uu.Actual_Break, uu.Actual_Travel, uu.Verified_By,

uu.Verification_Date, uu.Call_Logged_Or_Signed, uu.History_Rec_ID,

y.Start_of_Week, x.Country, x.Area, x.Region, uu.Verified,

y.Log_ID,dd.effective_From

FROM dbo.tbl_History_Duties uu LEFT OUTER JOIN

dbo.tbl_Employees ON uu.Employee_No = dbo.tbl_Employees.Employee_No LEFT OUTER JOIN

dbo.tbl_History as y ON uu.History_Rec_ID = y.History_Rec_ID LEFT OUTER JOIN

dbo.tbl_Sites as x ON y.Site_Ref = x.Site_Ref

LEFT OUTER JOIN

(select top 1 * from

tbl_Site_parameters z

order by z.effective_from desc ) dd ON dd.site_ref = x.Site_Ref and dd.effective_from <= y.Start_of_Week

Pete

David Baldwin SSC Enthusiast Points: 161 More actions · Answer 5

Can't see my last reply, perhaps I did not actually send it, Anyway thanks for your thoughts. The problem is based on having to have the WHERE clause inside the derived table as it would otherwise produce no or many records depnding on how its ran, (There are multiple records that would match the criteria, I only need the top 1 (Most recent))

Solution was simple with very little overhead, I created a function thus:

CREATE FUNCTION GetEffectiveFrom(@date datetime,@site as nvarchar(10))

RETURNS datetime AS

BEGIN

RETURN (select top 1 effective_From from tbl_site_Parameters where site_ref = @site and Effective_From <= @date Order by effective_from desc)

END

I use it as:

FROM dbo.tbl_History_Duties uu LEFT OUTER JOIN

dbo.tbl_Employees ON uu.Employee_No = dbo.tbl_Employees.Employee_No LEFT OUTER JOIN

dbo.tbl_History as y ON uu.History_Rec_ID = y.History_Rec_ID LEFT OUTER JOIN

dbo.tbl_Sites as x ON y.Site_Ref = x.Site_Ref

LEFT OUTER JOIN

tbl_Site_parameters

dd ON dd.site_ref = x.Site_Ref and dd.effective_from = dbo.geteffectivefrom(y.start_of_Week,x.Site_Ref)

There may be other simple solutions, but this one works so I am less stressed thanks for your help.