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?

:banghead:

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?

Code: Select all

SELECT s.name FROM enrollment e

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?

Code: Select all

SELECT s.name FROM enrollment e
You would still need to INNER JOIN the students table by their student id.