MYSQL Join puzzle: inner and left join at the same time

Question

I have the following tables:

Schools
Teachers
Courses
XRefSchoolsTeachers
XRefCoursesTeachers

Now I am trying to get the list of all the teachers at the college the course is offered at, which are NOT teaching that course. So far I have:

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

which gives me the IDs of all the teachers at the school which the course is offered at, but now I need to remove all the IDs of teachers which are not against the ID of that course in XRefCoursesTeachers. I looked at another question here about how to remove things not on another list, and I need a left join for that. But I feel like I need an inner join too so that I only get teachers from that school which are not on the list already ><

e.g.

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

will give me all the teachers at any other course at any other school - not what I want. I want all the teachers which could teach this course but are not.

It is like I need an inner join on the XRefCourseTeachers.CourseID = Courses.ID, and then a left join on the same instance of XRefCourseTeachers using TeacherID so that I can pick out the NULL results. But I don't get how to do this.

How is this possible? Am I taking the correct approach?

Not quite. It turns out that each course can only be taught at a single school, and so the `Courses` table includes a `SchoolID` column. It might be better design to have that extra table, but for now its simply a column. — Sophie, Sep 18 '12 at 12:53
No need of re-posting the same solution as given by Clodoaldo. Rather you should vote and accept his solution — Sami, Sep 19 '12 at 16:21
@sami Actually his solution doesn't work and isn't the same as the one I posted. Pls see the comment I posted on his question. — Sophie, Sep 24 '12 at 08:47

Clodoaldo Neto · Answer 1 · 2012-09-18T13:11:39.993

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 = ?

or

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 = ?

edited Sep 18 '12 at 13:11

answered Sep 18 '12 at 13:02

Clodoaldo Neto

118,695
26
233
260

Of course!!~ I forgot inner join can also be done in the `WHERE` clause :D Thanks – Sophie Sep 19 '12 at 03:46
On closer inspection, your first expression is basically what I stated in the question, and it doesn't work – Sophie Sep 19 '12 at 12:23

score 1 · Accepted Answer · answered Sep 19 '12 at 12:28

Found the solution:

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 
        XRefCoursesTeachers.TeacherID IS NULL
    AND
        Cousers.ID = ?

MYSQL Join puzzle: inner and left join at the same time

2 Answers2