Page 1 of 1
Left join query ... duplicate results
Posted: Fri Apr 24, 2009 3:49 am
by johnc71
I have three tables (see attached image) I would like to get the names of all students who are not enrolled in English class. When I ran the query:
Code: Select all
SELECT s.name FROM students s LEFT JOIN enrollment e ON s.student_id = e.student_id WHERE e.class_id != 2
I get:
Mary
Terry
John
Mark
Terry (student id 1) should not be listed. Also, Stephanie, James, and Irena should be listed.
I guess the type of JOIN I am using is not correct?

Re: Left join query ... duplicate results
Posted: Fri Apr 24, 2009 4:22 am
by susrisha
Code: Select all
Select students.name from students,enrollment Where enrollment.class_id!=2;
try this
Re: Left join query ... duplicate results
Posted: Fri Apr 24, 2009 4:30 am
by viraj
actually johnc71 your code is ok.
Terry (student id 1) is in the out put because he also enrolled in Chemistry class. ( check enrollment id = 3 )
Re: Left join query ... duplicate results
Posted: Fri Apr 24, 2009 4:53 am
by johnc71
viraj wrote:actually johnc71 your code is ok.
Terry (student id 1) is in the out put because he also enrolled in Chemistry class. ( check enrollment id = 3 )
The code is doing what it needs to do. However, the results are not right for me. so, I guess I need a new code.
I need code to, on the following question, "Give me the names of ALL students in table students, who are not enrolled in history" will give me the following results.
John
Mary
Mark
Stephanie
James
Irena
Re: Left join query ... duplicate results
Posted: Fri Apr 24, 2009 6:07 am
by viraj
This will work.
Code: Select all
SELECT s.name FROM students s WHERE s.name NOT IN (SELECT s.name FROM enrollment e WHERE s.student_id = e.student_id AND e.class_id = 3)
3 - history
John is the only student doing history so his record will not be in the output
Code: Select all
SELECT s.name FROM students s WHERE s.name NOT IN (SELECT s.name FROM enrollment e WHERE s.student_id = e.student_id AND e.class_id = 2)
2 - English
Only Terry is doing English and his record will not be displayed.
There may be a left join version for this.
Re: Left join query ... duplicate results
Posted: Fri Apr 24, 2009 1:26 pm
by johnc71
The code works but can you please explain this part of the code. Enrollment table does not have names so how does this code works?
Re: Left join query ... duplicate results
Posted: Fri Apr 24, 2009 2:29 pm
by John Cartwright
johnc71 wrote:The code works but can you please explain this part of the code. Enrollment table does not have names so how does this code works?
You would still need to INNER JOIN the students table by their student id.