SQL join headache

[johnc71]

I want to get names of students that are not enrolled in "history" class. When I do the qyery below I get duplicate results.   

 

$sql="SELECT FROM students, enrollment WHERE enrollment.class_id NOT IN ('3')";

 

[Maq]

You need to join on the duplicate columns.

 

Try:

 

SELECT s.name FROM students s LEFT JOIN enrollment e ON s.student_id = e.student_id WHERE e.class_id != 3

[johnc71]

Thanks for your help.  I tried your query but there are still issues.  If the student is enrolled in more than 1 class, lets say student #1, he will still show in query results. 

 

Also, I would like to get a list of all students who are not enrolled in the particular class, not only the ones listed in the enrollment table.  Sorry if this is confusing. 

 

[johnc71]

I could not modify the post above so I am posting this as edit to make it more clear.

 

If I ran the following:

SELECT s.name FROM students s LEFT JOIN enrollment e ON s.student_id = e.student_id WHERE e.class_id != 2

 

I get the following results:

 

Mary

Terry

John

Mark

 

Isues:

Terry should not be listed. 

 

Also, Stephanie, James and Irena should be listed.  Hope this is more clear.

[fenway]

You can't filtered on LEFT JOIN'ed results -- move that condition to the on clause.