MYSQL连接拼图: inner and left join at the same time

Question

我有以下表格:

Schools
Teachers
Courses
XRefSchoolsTeachers
XRefCoursesTeachers

现在我正在尝试获取开设该类(class)的大学所有教师的名单，这些教师不教授该类(class)。到目前为止，我有:

SELECT *
FROM Courses
INNER JOIN XRefSchoolsTeachers
    ON Courses.SchoolID = XRefSchoolsTeachers.SchoolID

这给了我开设该类(class)的学校所有教师的 ID，但现在我需要删除所有不反对 XRefCoursesTeachers 中该类(class) ID 的教师 ID >。我看了another question here关于如何删除不在另一个列表中的内容，为此我需要一个左连接。但我觉得我也需要一个内部加入，这样我就只能得到那些不在名单上的学校的老师 ><

例如

SELECT *
FROM Courses
INNER JOIN XRefSchoolsTeachers
    ON Courses.SchoolID = XRefSchoolsTeachers.SchoolID
LEFT JOIN XRefCoursesTeachers
    ON (XRefCoursesTeachers.TeacherID = XRefSchoolsTeachers.TeacherID
     AND XRefCoursesTeachers.CourseID = Courses.ID)
WHERE Courses.ID = ? AND XRefSchoolsTeachers.TeacherID IS NULL

会给我任何其他学校任何其他类(class)的所有老师——这不是我想要的。我想要所有可以教这类(class)但不是的老师。

这就像我需要在 XRefCourseTeachers.CourseID = Courses.ID 上进行内部联接，然后在 XRefCourseTeachers 的同一实例上使用 进行左联接TeacherID 以便我可以挑选出 NULL 结果。但我不知道该怎么做。

这怎么可能？我采取的方法是否正确？

Answer 1

SELECT *
FROM Courses
INNER JOIN XRefSchoolsTeachers
    ON Courses.SchoolID = XRefSchoolsTeachers.SchoolID
left join XRefCoursesTeachers
    on XRefCoursesTeachers.TeacherID = XRefSchoolsTeachers.TeacherID
where 
    XRefCoursesTeachers.TeacherID is null
    and
    Cousers.ID = ?

或

SELECT *
FROM Courses
INNER JOIN XRefSchoolsTeachers
    ON Courses.SchoolID = XRefSchoolsTeachers.SchoolID
where 
    XRefCoursesTeachers.TeacherID not in (
        select TeacherID
        from XRefCoursesTeachers
        where XRefCoursesTeachers.CourseID = Courses.ID
    )
    and
    Courses.ID = ?