I am attempting to produce a table that reveals which instructors are teaching courses that they are not certified to teach.

I have two tables of data:

certification

taught_courses

I was thinking about producing a table that looks like this (I'm sure there are alternative options):

Here's my current code, which produces a table with too many rows, making cross-validation for subject_taught with subject_cert tedious:

SELECT
    t.instructor,
    t.subject_taught,
    c.subject_cert
FROM taught_course AS t
LEFT JOIN certification AS c
    ON t.instructor = c.instructor_id
ORDER BY
    t.instructor,
    t.subject_taught,
    c.subject_cert ASC;

What suggestions would you have? Any help will be sincerely appreciated, thank you so much for your time.

CodePudding user response：

I think you are looking for

SELECT
    t.instructor,
    t.subject_taught,
    c.subject_cert
FROM taught_course AS t
LEFT JOIN certification AS c
    ON t.instructor = c.instructor_id AND t.subject_taught = c.subject_cert
WHERE c.instructor_id IS NULL
ORDER BY
    t.instructor,
    t.subject_taught,
    c.subject_cert ASC;

This is your query, but I added a second condition (AND t.subject_taught = c.subject_cert) to the join since you want to match the subjects as well as the instructors and I added WHERE c.instructor_id IS NULL since you only want to return rows where the left join failed to find a match.