Untitled

SQL: INNER JOIN   NOT EXIST
EnrollID     UserID     SubjID
1            1          1
2            1          2
3            1          3
4            3          1
5            7          2

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

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

UserID
2
3

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

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)

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

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