-1

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.stack.imgur.com/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)))

khandro
  • 3
  • 2
  • 1
    [Images](//meta.stackoverflow.com/q/285551/90527) should not be used for textual data, such as code, error messages, or sample data/schema. – outis Apr 01 '23 at 03:31
  • 1
    Since SQL includes data definition, a [mcve] for an [SQL question](//meta.stackoverflow.com/q/333952/90527) should include [DDL](//en.wikipedia.org/wiki/Data_definition_language) statements for sample tables (rather than an ad hoc table specification) and [DML](//en.wikipedia.org/wiki/Data_manipulation_language) statements for sample data (rather than a dump or ad hoc format). Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. – outis Apr 01 '23 at 03:31
  • 1
    See the [help] for more on [how to ask good questions](/help/how-to-ask) and many other helpful topics. See also the [tips for asking a good Structured Query Language (SQL) question](//meta.stackoverflow.com/questions/271055). – outis Apr 01 '23 at 03:31
  • Please ask 1 specific researched non-duplicate question. Please either ask re 1 bad query/function with obligatory [mre], including why you think it should return something else or are unsure at the 1st subexpression that it doesn't give what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & a [mre]. Then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. [ask] [Help] Basic questions are faqs. – philipxy Apr 01 '23 at 05:29
  • [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) – philipxy Apr 01 '23 at 05:31

1 Answers1

0
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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Gmanicus
  • 70
  • 6
  • 1
    This did it! The combination of join statements AND subqueries helped solve this perfectly. Also, changing the operand from IN to NOT IN in line 5 was crucial. Thank you so much! – khandro Apr 03 '23 at 16:17