Again, your WHERE clause is converting your OUTER JOIN to an INNER JOIN. You want to find record where the value is MISSING. Use the following.

SELECT DISTINCT

CourseInfo.CourseStateCode ,

CourseInfo.CourseTeacherID ,

CourseInfo.SchoolCourseID ,

Responsibility.TeacherID ,

Responsibility.SPN ,

Responsibility.TeacherName ,

Responsibility.SubjectCode

FROM CourseInfo

LEFT JOIN Responsibility ON CourseInfo.CourseTeacherID = Responsibility.TeacherID

AND CourseInfo.CourseStateCode = Responsibility.SubjectCode

WHERE Responsibility.TeacherID IS NULL;

I also changed your join criteria. You should avoid calculations in JOIN and WHERE criteria whenever possible, because it prevents the optimizer from using a seek. Also, this particular calculation will produce the same results for different pairs producing false matches.

Finally, some of the tables that you had in your original derived table need to be moved to the outer query, so that the teacher's name and the SPN can be included in your output. Since you are specifically looking for rows with missing information, and you have set it up so they are only appearing in the side that's missing, they will be missing in the results.

Drew