Can I join on an alias?

Question

Can I join on an alias?

krypto69

SSChampion

Points: 13609
More actions
July 18, 2013 at 12:58 pm

#395165

Is it possible to create a join on an alias?
I have this query that I need to join to a table (month):
SELECT
pehPErcontrol,case left(substring(pehPErcontrol,5,len(pehPErcontrol)),2) when '01' then 'January' when '02' then 'Feburary' when '03' then 'March' when '04' then 'April' when '05' then 'May' when '06' then 'June' when '07' then 'July' when '08' then 'August' when '09' then 'September' when '10' then 'October' when '11' then 'November' when '12' then 'December' end as month,
rtrim(eepNameLast) +
', ' + rtrim(eepNameFirst) +
' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name,
eepNameLast AS [Last Name],
IsNull(eepNameSuffix,'') AS [Suffix],
eepNameFirst AS [First Name],
IsNull(eepNameMiddle,'') AS [Middle Name],
pehCurAmt AS [Current Amount],
pehCurHrs AS [Current Hours],
pehCoID AS [Company ID],
pehEEID AS [EE ID],
pehEmpNo AS [Emp No],
pehLocation AS [Location],
pehJobCode AS [Job Code],
pehOrgLvl1 AS [Org Level 1],
pehOrgLvl2 AS [Org Level 2],
pehOrgLvl3 AS [Org Level 3],
pehOrgLvl4 AS [Org Level 4],
pehPayGroup AS [Pay Group],
pehProject AS [Project],
pehShfShiftAmt AS [Shift Amount],
pehearncode AS [Earn Code]
FROM EmpPers JOIN pearhist
ON pehEEID = eepEEID
join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ON [orglevel] = pehOrgLvl2
inner join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] on [month] = v.month

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

No, you can't because the alias doesn't exist when the JOIN is processed.

More info: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/70efeffe-76b9-4b7e-b4a1-ba53f5d21916/order-of-execution-of-sql-queries

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61152 More actions · Answer 2

You can. It's hard to work with what you posted so I created the following sample code to demonstrate how you would join two tables in the way you are attempting:

DECLARE @table1 TABLE (event_id int primary key, event_date date not null);

DECLARE @table2 TABLE (mo varchar(20) unique not null);

INSERT INTO @table1 VALUES (1,'1/10/2013'),(2,'2/12/2013'),(3,'3/10/2013');

INSERT INTO @table2 VALUES ('January'),('February'),('March');

SELECT t1.event_id, t1.event_date, t2.mo

FROM @table1 t1

JOIN @table2 t2 ON DATENAME(MONTH,t1.event_date)=t2.mo

Also, your case statement for [month] is not necessary. Instead of this:

SELECT case left(substring(pehPErcontrol,5,len(pehPErcontrol)),2)

when '01' then 'January'

when '02' then 'Feburary'

when '03' then 'March'

when '04' then 'April'

when '05' then 'May'

when '06' then 'June'

when '07' then 'July'

when '08' then 'August'

when '09' then 'September'

when '10' then 'October'

when '11' then 'November'

when '12' then 'December'

end as [month],

You should do this:

SELECT DATENAME(MONTH,CAST(left(substring(pehPErcontrol,5,len(pehPErcontrol)),2) AS varchar(2))+'/01/1900') AS [month]

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61152 More actions · Answer 3

Luis Cazares (7/18/2013)
No, you can't because the alias doesn't exist when the JOIN is processed.
More info: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/70efeffe-76b9-4b7e-b4a1-ba53f5d21916/order-of-execution-of-sql-queries

You can get around that doing something like this (using my own sample code for simplicity):

DECLARE @table1 TABLE (event_id int primary key, event_date date not null);

DECLARE @table2 TABLE (mo varchar(20) unique not null);

INSERT INTO @table1 VALUES (1,'1/10/2013'),(2,'2/12/2013'),(3,'3/10/2013');

INSERT INTO @table2 VALUES ('January'),('February'),('March');

WITH x1 AS

(SELECT event_id, DATENAME(MONTH,event_date) AS [month]

FROM @table1)

SELECT x1.event_id, x2.mo

FROM x1

JOIN @table2 x2 ON x1.[month]=x2.mo

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Alan.B (7/18/2013)
You can. It's hard to work with what you posted so I created the following sample code to demonstrate how you would join two tables in the way you are attempting:

I partially disagree with you, Alan. You're joining by an expression which is completely valid, but joining by a clumn alias is not allowed.

EDIT: Yes, you can get around with CTEs, subqueries or expressions but it would stop being an alias. 😛 Am I being too strict here?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

krypto69 SSChampion Points: 13609 More actions · Answer 5

Gotcha. Thanks Guys!

Very helpful and informative.