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

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

Jeff Moden SSC Guru Points: 1004340 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)