Left Join Returning Extra Rows T-SQL?

Question

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?

Answer 1

The reason you're getting the extra rows is the fact you've added conditions in the where clause for the left join table. Move those conditions such as r.RESIDENT_COMMUTER='R' to the join clause and you won't get these issues.

For example

select * from ACADEMIC a
left join RESIDENCY r on a.PEOPLE_CODE_ID = r.PEOPLE_CODE_ID
and r.ACADEMIC_YEAR = (Select Year(GetDate()))
and r.RESIDENT_COMMUTER='R'
and r.ACADEMIC_TERM='Fall'
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%')

Answer 2

What's actually in your residency table? What does

SELECT 
  PEOPLE_CODE_ID, 
  COUNT(*) 
FROM 
  residency 
GROUP BY 
  PEOPLE_CODE_ID 
HAVING 
  COUNT(*) > 1

give as a result?

A LEFT JOIN will give you all the rows in your ACADEMIC table, regardless of whether or not there is a matching row in your RESIDENCY table. But if there are multiple matching rows in RESIDENCY for your join condition, you'll still get multiple rows back, just as you would with an INNER JOIN.

Answer 3

Not entirely sure why it is happening because I don't know the relationship between the two tables you are using (one to many, many to many, etc.). However, I can tell you that when you add a column from the "left joined" table in your WHERE clause, it cancels out the left join and makes it an inner join.

Answer 4

I'm doing a left join - why is it adding an extra row?

You are doing an INNER JOIN.

Conditions like this:

and r.ACADEMIC_TERM='Fall'

effectively filter out the fake rows produced by the left join, leaving only the records that would be returned by an INNER JOIN on the same conditions.

The reason because you are getting two records per academic is that there are two records in residency per academic that satisfy the other join condition.

If you want to return only one residency per academic, you need to define which one would it be.

Answer 5

Your join is taking rows that fit criteria from your left table, and pairing them with rows from the right table, in every case that "a.PEOPLE_CODE_ID = r.PEOPLE_CODE_ID" is true this combination will be returned. Obviously this condition is satisfied for 2 rows for every left hand row you have.

Answer 6

Let's explore two differnt options for getting what you want. First you need to fix the general query to move any where conditons on table r to the join. See this link to understand why what you are doing is incoorect if you really need a left join: http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN

And do not ever again use select * in production code, this is bad practice.

Here is the fixed query:

select [List columns here do not list the join column twice!] 
from ACADEMIC a 
left join RESIDENCY r on a.PEOPLE_CODE_ID = r.PEOPLE_CODE_ID 
and r.RESIDENT_COMMUTER='R'
and r.ACADEMIC_TERM='Fall' 
and r.ACADEMIC_YEAR = (Select Year(GetDate())) 
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%')  

If that doesn;t give you waht you need, then you may need to do something like this:

select [List columns here  for a and r1, do not list the join column twice!] 
from ACADEMIC a 
left join(select min(people_code_id) from  RESIDENCY r on a.PEOPLE_CODE_ID = r.PEOPLE_CODE_ID 
    and RESIDENT_COMMUTER='R'
    and ACADEMIC_TERM='Fall' 
    and ACADEMIC_YEAR = (Select Year(GetDate())) ) r on a.PEOPLE_CODE_ID = r.PEOPLE_CODE_ID 
left join RESIDENCY r1 on r.PEOPLE_CODE_ID = r1.PEOPLE_CODE_ID 
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%')