Select command to list instructors

1

Hi,

I have this table

+------------+----------+---------------+
| FacultryID | CourseID | DateQualified |
+------------+----------+---------------+
|       2143 | ISM 3112 | 9/1988        |
|       2143 | ISM 3113 | 9/1988        |
|       3467 | ISM 4212 | 9/1995        |
|       3467 | ISM 1930 | 9/1996        |
|       4756 | ISM 3113 | 9/1991        |
|       4756 | ISM 3112 | 9/1991        |
|       1111 | ISM 3113 | 9/1996        |
+------------+----------+---------------+

Which instructors can teach either 'ISM 3113' or 'ISM 3112', but cannot teach both?
In this table FacultyID 2143 and 4756 teach both, so I should get the result with FacultyID 1111 only.

How to list that with select command?

2 
SELECT FacultyID
FROM tablename
WHERE CourseID IN ('ISM 3112', 'ISM 3113') --corrected per bitsmed
GROUP BY FacultyID
HAVING MAX(CASE WHEN CourseID = 'ISM 3112' THEN 1 ELSE 0 END) +
    MAX(CASE WHEN CourseID = 'ISM 3113' THEN 1 ELSE 0 END) = 1
3 Likes
3

Love this solution, but a minor correction should be done:

WHERE CourseID IN ('ISM 3112', 'ISM 3113')
1 Like
4

Thanks ScottPletcher & bitsmed, it works like charm!