Python MYSQL comparing multiple tables with OR and AND operators

Question

I have a task where I have 3 tables in MYSQL database.

A task involves checking Table1 and comparing it to Table2 and Table3 whether all required recipe items are matched. If not, return an error.

Table1 - is a recipe table. It shows what items are required to complete a task.

Table2 - Describes what items first person have.

Table3 - Describes what items second person have.

If there is a single item in a recipe table that neither person have, I must return an error because the recipe cannot be made because ingredient is missing.

I am adding a picture that represents my mysql database tables:

From the tables above, I am expecting to get an error message because neither of persons have banana. If I assign banana to one of the table2 or table3, the error should go away.

What I have tried so far:

SELECT
  item
FROM
  Recipe_Table
WHERE(
    Recipe_Table.item NOT IN(
      SELECT
        item
      from
        User1_table
    )
    AND Recipe_Table.item NOT IN(
      SELECT
        item
      from
        User2_table
    )
  )

But it does not work as I expected. I have tried a case where one of the persons have all the required ingredients and this mysql query still returns me that something is missing.

It is also quite important to me that the query is simple to understand and ideally it should be just 1 query instead of multiple ones.??

Hoping to get some good advice. Thanks in advance.

question from:https://stackoverflow.com/questions/65884357/python-mysql-comparing-multiple-tables-with-or-and-and-operators

Answer 1

Waitting for answers