Left Join Returning Extra Rows T-SQL?

Posted by davemackey on Stack Overflow See other posts from Stack Overflow or by davemackey
Published on 2010-05-07T14:51:30Z Indexed on 2010/05/07 14:58 UTC
Read the original article Hit count: 258

Filed under:
|
|
|

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?

© Stack Overflow or respective owner

Related posts about t-sql

Related posts about tsql