alias then join

Question

Post reply

alias then join

Richard Holloway

SSC Enthusiast

Points: 190
More actions
September 15, 2010 at 11:11 am

#387793

I have two databases I need to join on. Issue is the project number on one is missing fist two digits. I need to append to that number then match against other database field and return a result. Here is the query I am trying.
SELECT *
FROM TimeDetailNew TimeAlias
LEFT OUTER JOIN PM1.PM70.Projects PM1Alias
ON TimeAlias.[Worked Project] = PM1Alias.MJH_Number
WHERE (PM1Alias.MJH_Number = '10' + TimeAlias.[Worked Project])

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

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Sergiy

SSC Guru

Points: 110208

September 15, 2010 at 2:48 pm

#1221712

Does it work?

_____________
Code for TallyGenerator

Richard Holloway SSC Enthusiast Points: 190 More actions · Answer 2

No. It compiles fine but when it runs it does not match whats in the MJH_Number column. I get NULL's nor does it concat the [worked task] column.

Cory E. SSCertifiable Points: 7076 More actions · Answer 3

Richard Holloway (9/15/2010)
I have two databases I need to join on. Issue is the project number on one is missing fist two digits. I need to append to that number then match against other database field and return a result. Here is the query I am trying.
SELECT *
FROM TimeDetailNew TimeAlias
LEFT OUTER JOIN PM1.PM70.Projects PM1Alias
ON TimeAlias.[Worked Project] = PM1Alias.MJH_Number
WHERE (PM1Alias.MJH_Number = '10' + TimeAlias.[Worked Project])

Try placing the same "join" in the where clause on the join

SELECT *

FROM TimeDetailNew TimeAlias

LEFT OUTER JOIN PM1.PM70.Projects PM1Alias

ON '10' + TimeAlias.[Worked Project] = PM1Alias.MJH_Number

-- Cory

Richard Holloway SSC Enthusiast Points: 190 More actions · Answer 4

Cool! That worked...now how do I keep it from selecting NULL in the MJH_Number column? Its over 160852 rows and takes a while but if I eliminate the NULL's that would be that much quicker. 😉

Cory E. SSCertifiable Points: 7076 More actions · Answer 5

Cory E.

SSCertifiable

Points: 7076

September 15, 2010 at 2:57 pm

#1221728

inner join

-- Cory

Richard Holloway SSC Enthusiast Points: 190 More actions · Answer 6

SWEET! That worked...Pretty easy now that I look back at it. Thanks a MILLION!! 😀