Left Outer Join replace *=

Question

Post reply

Left Outer Join replace *=

kd11

Hall of Fame

Points: 3447
More actions
July 2, 2010 at 1:10 pm

#222799

I'm a System Admin doing getting into SQL, is the correct way to re-write this statement

(OLDWay)

select Fname,LName,Mname

from Employee, Timekeep where (employee.id *= timekeep.id

and (timekeep.dept <> 'TRY' and timekeep.wvgroup <> 'SEC' and and time.uname <> 'smith'))

I think the new way would be like

select tkinit,tklast,tkfirst,uname

from Employee a Left outer Join TimeKeep b

on a.id = b.id

where a.wvgroup != 'TIMENTRY'

and timekeep.dept != 'TRY' and

and timekeep.wvgroup != 'SEC'

and time.uname <> 'smith'

I don't get the same results in the new one as I do with the first one.

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

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

sturner SSC-Insane Points: 22411 More actions · Answer 1

old way:

select Fname,LName,Mname

from Employee, Timekeep where (employee.id *= timekeep.id

and (timekeep.dept <> 'TRY' and timekeep.wvgroup <> 'SEC' and and time.uname <> 'smith'))

new way:

select Fname,LName,Mname

from Employee

LEFT OUTER JOIN Timekeep ON employee.id = timekeep.id

and (timekeep.dept <> 'TRY' and timekeep.wvgroup <> 'SEC' and timekeep.uname <> 'smith')

I am assuming you meant timekeep.uname instead of time.uname. One of the reasons to abandon the old TSQL style join syntax was because it was often incapable of producing the same results as ANSI join syntax.

The probability of survival is inversely proportional to the angle of arrival.

CavyPrincess Hall of Fame Points: 3336 More actions · Answer 2

CavyPrincess

Hall of Fame

Points: 3336

July 2, 2010 at 1:47 pm

#1188413

kd11,

Your "new query" contains this restriction - where a.wvgroup != 'TIMENTRY'

I don't see this in the "old query". Do you get the same results when this is either added ot the old query or removed from the new one?

Cindy

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 3

How's this:

select



   Fname

 , LName

 , Mname

from Employee e

  LEFT OUTER JOIN Timekeep t

    ON e.employee.id = t.timekeep.id

WHERE t.dept <> 'TRY'

and t.wvgroup <> 'SEC'

and t.uname <> 'smith'

Keep in mind those <> (or !=) force a scan of the entire tables. Wasn't sure of that last one was employee or timekeep.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Steve Jones - Editor (7/2/2010)

How's this:

select
Fname , LName , Mname from Employee e LEFT OUTER JOIN Timekeep t ON e.employee.id = t.timekeep.id WHERE t.dept <> 'TRY' and t.wvgroup <> 'SEC' and t.uname <> 'smith'

Keep in mind those <> (or !=) force a scan of the entire tables. Wasn't sure of that last one was employee or timekeep.

I got all SEEKs on a similar test. The ON criteria is what makes that possible (I believe).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions