Only show Warehous where product is not existing - multiply tables mysql

Question

I've got three tables. Product, Warehouse and IsIn.

Product:

productID
name
price

Warehouse:

warehousID
name

IsIn:

productID*
warehousID*
quantity

I now want to write a question where i get all warehouses where a specific product does not exist.

I can get a table where the specific product are, but when i try to get where it's not, I get the wrong answer.

Does anyone know how i can write the question?

Does this answer your question? [Find records from one table which don't exist in another](https://stackoverflow.com/questions/367863/find-records-from-one-table-which-dont-exist-in-another) — June7, Mar 07 '21 at 22:34

score 0 · Accepted Answer · answered Mar 07 '21 at 23:12

I now want to write a question where i get all warehouses where a specific product does not exist.

This sounds like a NOT EXISTS query:

select w.*
from warehouse w
where not exists (select 1
                  from isin i
                  where i.warehouseid = w.warehouseid and
                        i.productid = ?
                 );

You may want to add and i.quantity > 0 in the subquery. It is not clear if quantity is important for the question.

Only show Warehous where product is not existing - multiply tables mysql

1 Answers1