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?
Advertisement
Answer
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:
x
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.