Left join query ... duplicate results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
johnc71
Forum Newbie
Posts: 10
Joined: Tue Jun 24, 2008 2:19 am

Left join query ... duplicate results

Post 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:
Attachments
sql_problem.gif
sql_problem.gif (4.16 KiB) Viewed 964 times
User avatar
susrisha
Forum Contributor
Posts: 439
Joined: Thu Aug 07, 2008 11:43 pm
Location: Hyderabad India

Re: Left join query ... duplicate results

Post by susrisha »

Code: Select all

 
Select students.name from students,enrollment Where enrollment.class_id!=2;
 
try this
User avatar
viraj
Forum Newbie
Posts: 11
Joined: Wed Nov 19, 2008 7:52 am
Location: Colombo - Sri Lanka

Re: Left join query ... duplicate results

Post 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 )
johnc71
Forum Newbie
Posts: 10
Joined: Tue Jun 24, 2008 2:19 am

Re: Left join query ... duplicate results

Post 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
User avatar
viraj
Forum Newbie
Posts: 11
Joined: Wed Nov 19, 2008 7:52 am
Location: Colombo - Sri Lanka

Re: Left join query ... duplicate results

Post 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.
johnc71
Forum Newbie
Posts: 10
Joined: Tue Jun 24, 2008 2:19 am

Re: Left join query ... duplicate results

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Left join query ... duplicate results

Post 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.
Post Reply