Left Join Returning Extra Rows T-SQL?
- by davemackey
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?
