How to join three tables with multiple where conditions?

The problem is related to our class database, and it asks us to, " List all faculty that have offices in the Business building but don’t teach in the Business building".

My first two queries work correctly in finding those three faculty who have an office in bus, and my final query worked on its own to find those who don't teach in the business building, but when they're combined I expect it to narrow it down to one faculty, instead it produces the result of my first two query statements.

I've tried the code below as well as multiple variations + join variations and have gotten it to produce two names instead of the current three, but never just the one i'm after. I searched through a few related posts and tried a few of their solutions but none of them worked for me.

I know that from looking at the raw data, the only name that should populate with the final query is Jerry Williams(f_id 3), however, I can't figure out how to use both necessary where clauses to make sure the faculty has their office in the bus building AND that they don't teach in the bus building.

I thought it should return Jerry Williams because of my final where statement which includes an and statement to filter the results I was looking for. Instead it is only including the results from the query above which states the three faculty with offices in the bus building.

Faculty table data faculty

Location table data location

Course_section table data course_section

Here is my current code(data included above): [current mysql script][1] [1]: https://i.sstatic.net/Uv5Ed.png

select f_first,f_last from faculty where loc_id in ( select loc_id from location where bldg_code in ( select bldg_code from course_section where (bldg_code = "BUS") and (loc_id <> 5 and loc_id <> 6 and loc_id <> 7 and loc_id <> 8)))

Solution

SELECT f_name, l_name FROM faculty
INNER JOIN location -- Only keep records that match both a faculty & a...
    ON location.loc_id = faculty.loc_id
    AND location.bldg_code = 'BUS' -- ...Business location
WHERE faculty.f_id NOT IN ( -- Only select faculty...
    SELECT f_id FROM course_section
    WHERE course_section.f_id = faculty.f_id -- ...if they do not have a course
    AND course_section.loc_id IN (
        SELECT loc_id FROM location
        WHERE location_loc_id = course_section.loc_id
        AND location.bldg_code = 'BUS' -- ...in the Business building
    )
)

The INNER JOIN keeps only the faculty that work in the business building, then the WHERE clause checks ...

that the faculty id is not found
... in a list of their courses
... that also have a loc_id in a location with the Business code

Not tested.