SQL: INNER JOIN + NOT EXIST

Question

I am trying to create an SQL statement where I need to join 3 tables

EnrollStudents

EnrollID     UserID     SubjID
1            1          1
2            1          2
3            1          3
4            3          1
5            7          2

Students

StudentID     UserID     YearID
1             1          1
2             3          1
3             7          1

Subjects

SubjID     SubjCode     YearID
1          English      1
2          Math         1
3          Science      1

and the output should be...

UserID
2
3

since User 1 already enrolled all the subjects, while User 3 and User 7 are still shown since some subjects are still not enrolled.

I have the following SQL statement with no luck:

SELECT Students.UserID 
FROM Students 
WHERE Students.YearID = 1 
    AND Students.UserID NOT IN (SELECT EnrollStudents.UserID 
                                FROM EnrollStudents)

Any ideas?

Answer 1

SELECT s.UserID
FROM Students AS s
LEFT OUTER JOIN EnrollStudents AS es ON s.UserID = es.UserID
GROUP BY s.UserID
HAVING COUNT(DISTINCT(es.SubjID)) < (SELECT COUNT(*) FROM Subjects)

...wait a minute. I think you're mixing up your "StudentID" and "UserID" in your sample output and EnrollStudents table.

http://sqlfiddle.com/#!3/61618/1