LEFT JOIN Returning Multiple Rows? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 JOIN Returning Multiple Rows?

Author

Topic

davidshq
Posting Yak Master

119 Posts

Posted - 2010-05-07 : 10:23:16

I have the following query:


select * from ACADEMIC a
	left join RESIDENCY r on a.PEOPLE_CODE_ID = r.PEOPLE_CODE_ID
	where a.ACADEMIC_TERM='Fall' 
	and r.ACADEMIC_TERM='Fall'
        and a.ACADEMIC_SESSION=''
	and a.ACADEMIC_YEAR = (Select Year(GetDate())) 
	and r.ACADEMIC_YEAR = (Select Year(GetDate()))
	and (CLASS_LEVEL LIKE 'FR%'
		 OR a.CLASS_LEVEL LIKE 'SO'
		 OR a.CLASS_LEVEL LIKE 'JR'
		 OR a.CLASS_LEVEL LIKE 'SR%') 
	and r.RESIDENT_COMMUTER='R'

For each person in the database it returns two rows with identical information. Yet, when I do the same query without the left join:


select * from ACADEMIC a
	where a.ACADEMIC_TERM='Fall' 
        and a.ACADEMIC_SESSION=''
	and a.ACADEMIC_YEAR = (Select Year(GetDate())) 
	and (CLASS_LEVEL LIKE 'FR%'
		 OR a.CLASS_LEVEL LIKE 'SO'
		 OR a.CLASS_LEVEL LIKE 'JR'
		 OR a.CLASS_LEVEL LIKE 'SR%') 
		 ORDER BY PEOPLE_ID

It returns only one row for each person. I'm doing a left join - why is it adding an extra row? Shouldn't it only do that if I add a right join?
Thanks,
Dave.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-05-07 : 12:21:43

1. you seem to be missing a further "qualifier" on your join statement - you'll have to look at the data to see what needs to be in place.
2. switching to a "right join" won't solve anything in this case.
3. does the below change the result?

select * from ACADEMIC a
left join RESIDENCY r on a.PEOPLE_CODE_ID = r.PEOPLE_CODE_ID
and a.ACADEMIC_TERM = r.ACADEMIC_TERM
and a.ACADEMIC_YEAR = and r.ACADEMIC_YEAR
and r.RESIDENT_COMMUTER='R'
where a.ACADEMIC_TERM='Fall'
and a.ACADEMIC_SESSION=''
and a.ACADEMIC_YEAR = (Select Year(GetDate()))
and (CLASS_LEVEL LIKE 'FR%'
OR a.CLASS_LEVEL LIKE 'SO'
OR a.CLASS_LEVEL LIKE 'JR'
OR a.CLASS_LEVEL LIKE 'SR%')

4. can you supply sample input data and "matching" expected results? See FAQ's for examples.
5. you use some "LIKE" clauses with no "%"...these may better be expressed as "=" clauses
6. when using a LEFT JOIN and then including a reference to a column of the "joined-to" table in the WHERE clause, will implicitly turn the LEFT join into an INNER join unless you move that code up to the ON clause "...and r.RESIDENT_COMMUTER='R'"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources