How do I query a set that doesn't contain any members from another set in MySQL?

Question

I'd like to return a list of students who have taken classes in one department but not in another. Here's my query and its result is coming up blank for some reason.

 SELECT *
 FROM student
 JOIN transcript
    ON student.id = transcript.studID
 JOIN course
    ON transcript.crsCode = course.crsCode
 WHERE deptId = "CSCI" NOT IN
      (
        SELECT student.name
        FROM student
        JOIN transcript
          ON student.id = transcript.studID
        JOIN course
          ON transcript.crsCode = course.crsCode
        WHERE deptId = "MATH"
);

Here are what the tables look like:

 Student (id, name, address, status)
 Transcript (studId, crsCode, semester, grade)
 Courses (crsCode, deptId, crsName, descr)

Answer 1

Without using sub queries:-

SELECT DISTINCT student.*
FROM student
JOIN transcript
ON student.id = transcript.studID
INNER JOIN course c1
ON transcript.crsCode = c1.crsCode
AND c1.deptId = 'CSCI'
LEFT OUTER JOIN course c2
ON transcript.crsCode = c2.crsCode
AND c2.deptId = 'MATH'
WHERE c2.crsCode IS NULL

This is joining student against transcript. It then joins against course twice, once for the course you want and a LEFT OUTER JOIN for the course you don't want. The WHERE clause checks that there was no match on the course you do not want.

The DISTINCT is used to limit the results to single occurrences. This may not be necessary, but that depends on whether a single student can have done courses multiple times.

Answer 2

You could use two exists conditions - one for the department you want to include and one for the department you want to exclude.

SELECT s.*
FROM   student s
WHERE  EXISTS (SELECT *
               FROM   transcript t
               JOIN   courses c ON t.crsCode = c.crsCode
               WHERE  deptId = 'MATH' AND t.studId = s.id) AND
       NOT EXISTS (SELECT *
               FROM   transcript t
               JOIN   courses c ON t.crsCode = c.crsCode
               WHERE  deptId = 'CSCI' AND t.studId = s.id)

Answer 3

To check two conditions you need to add AND clause in your query and check student.name is NOT IN in your sub-query as:

 SELECT *
 FROM student
 JOIN transcript
    ON student.id = transcript.studID
 JOIN course
    ON transcript.crsCode = course.crsCode
 WHERE deptId = "CSCI" AND student.name NOT IN
      (
        SELECT student.name
        FROM student
        JOIN transcript
          ON student.id = transcript.studID
        JOIN course
          ON transcript.crsCode = course.crsCode
        WHERE deptId = "MATH"
);