I have three tables. Container, Inventory and SKU
Container table
ContainerId |
---|
C1 |
C2 |
C3 |
Inventory Table
InventoryId | Sku | Container |
---|---|---|
I1 | S1 | C1 |
I2 | S1 | C2 |
I3 | S2 | C1 |
SKU Table
SkuId |
---|
S1 |
S2 |
S3 |
I need to create a sql query that will return the container(s) that contains all of the given SKU ids. So if I want the containers that have SKU S1, my results should be C1 and C2 but if I want to get the containers that have SKU ids S1 and S2 my only result should be C1. C2 should not be a result because it does not have S2.
So far the query I have tried is
SELECT distinct containerId FROM Container c JOIN Inventory i ON i.Container = c.ContainerId JOIN SKU s ON s.SkuId = i.Sku WHERE s.SkuId IN (S1, S2)
But the WHERE IN statement is a shorthanded OR so it will return C2 as a result too because it contains S1.
I know there is no way to shorthand an AND condition but is there a similar way or a possible way to do this?
Advertisement
Answer
Instead of distinct, you use group by and use count to verify that all required skus were present:
SELECT c.ContainerId FROM Container c JOIN Inventory i ON i.Container = c.ContainerId JOIN SKU s ON s.SkuId = i.Sku AND s.SkuId in (S1, S2) GROUP BY c.ContainerId HAVING COUNT(DISTINCT s.SkuId)=2
But, as commented by others, there’s no need to use any table but Inventory:
SELECT i.Container FROM Inventory i WHERE i.Sku in (S1, S2) GROUP BY i.Container HAVING COUNT(DISTINCT i.Sku)=2