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?

Solution 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

Solution 2

select s.UserID
from Students s
left outer join (
    select UserID
    from EnrollStudents
    group by UserID
    having count(distinct SubjID) = 3
) a on s.UserID = a.UserID
where a.UserID is null 
    and s.YearID = 1

SQL Fiddle Example

Solution 3

It looks like you are trying to qualify all students in their first year who have not enrolled in all the first year required classes, hence looking to get only student 2 and 3. Your data has everyone in the group of a single yearID, but I suspect you actually have data that spans multiple years and you are explicitly concerned with just those students in year 1, and those subjects that are ALSO associated with first year requirements.

The first query (YrSubjects) results will pre-aggregate the number of classes for the single year in question so it doesn't have to do this repeatedly for every student. Just once... With that as an unassigned JOIN to the rest of the query will be a Cartesian, but one record per person, no duplicates anyhow.

Next is the rest of the tables/joins. Get the students who are enrolled in subjects that are only associated with year 1. The where clause explicitly restricts down to only those "1st year" students.

The final HAVING clause applies the count of enrollments that are LESS then the total subjects for first year requirements. With this query, you are not "fixed" into a certain hard-coded number of subjects you are EXPECTING...

SELECT 
      S.StudentID 
   FROM 
      ( select count(*) as YrClasses 
           from 
              Subjects 
           where YearID = 1 ) YrSubjects,
      Students S
         JOIN EnrollStudents ES
            on S.UserID = ES.UserID
            JOIN Subjects S
               ON ES.SubjID = S.SubjID
              AND S.YearID = 1
   WHERE 
      S.YearID = 1 
   HAVING
      count(*) < YrSubjects.YrClasses