1

I have a table, where I am listing users attending a class. Below this table I want to have a <select> field, with the list of users that do NOT attend the class.

Query that lists users attending the class:

SELECT * FROM User
 left join UserCourse on User.id = UserCourse.fkStudentId
 where UserCourse.fkCourseId = 1 and u.fkRoleId = 3;

And here is my code, where I try to list the "exceptions":

SELECT * FROM User where id != 
(SELECT id FROM User
 left join UserCourse on User.id = UserCourse.fkStudentId
 where UserCourse.fkCourseId = 1 and u.fkRoleId = 3);

The problem is, that there are several rows that match the 2nd query, and that isn't allowed. Do you have any suggestions how to to list the students that don't attend?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Kriszta
  • 670
  • 1
  • 7
  • 22
  • Why use a `left join`? Is there a case where a User id is present in courses table but not in User table? Also, you could use `AND fkStudentID = NULL` to get students not attending. – shahkalpesh Feb 24 '13 at 20:38
  • possible duplicate of [multiple search condition sql](http://stackoverflow.com/questions/10341394/multiple-search-condition-sql) – gdoron Feb 24 '13 at 20:39
  • The proposed duplicate question is not a good match as a duplicate. – Jonathan Leffler Feb 24 '13 at 23:25

1 Answers1

5

Replace != with NOT IN:

SELECT * FROM User where id NOT IN
(SELECT id FROM User
 left join UserCourse on User.id = UserCourse.fkStudentId
 where UserCourse.fkCourseId = 1 and u.fkRoleId = 3);
gdoron
  • 147,333
  • 58
  • 291
  • 367