left outer join not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

left outer join not working

Author

Topic

thiyait
Yak Posting Veteran

70 Posts

Posted - 2007-10-12 : 08:38:31

I hope following both query does the same job but result will be differnt for me. can some one help me to explain the difference of two query

First query retrive the result as expected but second one didnt what is the problem.

select HexPayCategoryElement.payelementid,HexPayCategoryElement.payelementid,res.calculatedvalue
from HexPayCategoryElement HexPayCategoryElement,
Hexpaycalculatedresult res
where
HexPayCategoryElement.HRMPayrollCategoryID *= res.HRMPayrollCategoryID and
HexPayCategoryElement.payelementid *= res.payelementid and
HexPayCategoryElement.dataareaid *= res.dataareaid and
HexPayCategoryElement.payelementtype *= res.payelementtype and
res.dataareaid='glr'
and res.HRMPayCalendarID = 'Month'
and res.HRMPayPeriodId = 'Month001'
and res.emplid = 'emp006'
and HexPayCategoryElement.HRMPayrollCategoryID = 'Check'
order by sequence

select HexPayCategoryElement.payelementid,res.payelementid,res.calculatedvalue
from HexPayCategoryElement HexPayCategoryElement
left outer join Hexpaycalculatedresult res on
HexPayCategoryElement.HRMPayrollCategoryID =res.HRMPayrollCategoryID
and HexPayCategoryElement.payelementid=res.payelementid
and HexPayCategoryElement.dataareaid=res.dataareaid
and HexPayCategoryElement.payelementtype=res.payelementtype
where res.dataareaid='glr'
and res.HRMPayCalendarID = 'Month'
and res.HRMPayPeriodId = 'Month001'
and res.emplid = 'emp006'
and HexPayCategoryElement.HRMPayrollCategoryID = 'Check'
order by sequence

thanks in advance
thiya

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-10-12 : 09:04:00

Is your problem in the second query that you are getting to many rows being returned. Using a Left Outer Join will preserve the unmatched rows on the left table

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL

thiyait
Yak Posting Veteran

70 Posts

Posted - 2007-10-12 : 09:16:29

First query retrive the following

Month test1 987
month test2 NULL(not in second table)
Above is the correct one

second retrive as follows:(here test2 should come in the result)

Month test1 987

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-12 : 09:26:59

[code]select HexPayCategoryElement.payelementid,
res.payelementid,
res.calculatedvalue
from HexPayCategoryElement
left join Hexpaycalculatedresult as res on res.HRMPayrollCategoryID = HexPayCategoryElement.HRMPayrollCategoryID
and res.payelementid = HexPayCategoryElement.payelementid
and res.dataareaid = HexPayCategoryElement.dataareaid
and res.payelementtype = HexPayCategoryElement.payelementtype
and res.dataareaid = 'glr'
and res.HRMPayCalendarID = 'Month'
and res.HRMPayPeriodId = 'Month001'
and res.emplid = 'emp006'
where HexPayCategoryElement.HRMPayrollCategoryID = 'Check'
order by {table name or alias here}.sequence[/code]

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-12 : 09:56:17

thiyait -- be sure to read this:

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

You cannot add criteria in your WHERE clause that references columns in OUTER JOINed tables, because the NULL values will no longer be allowed and your join will no longer be an OUTER JOIN. Peso's solution should work for you, but be sure to read why.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources