Muralidhar Yaragalla Muralidhar Yaragalla - 5 months ago 8
SQL Question

How to select only the suppliers that supply all the required items?

There's a table which shows the relation between supplier and items. I want to get the suppliers that have both item 1 and 2.

tablepk supplierpk itempk
1 1 1
2 1 2
3 2 1

Answer

I guess you also have Suppliers table that lists all suppliers (and it has primary key id). If so, you can use 2 EXISTS statements, to verify that related table (you did not specify the name, so I called it SupplierItems) has both record with itempk=1 and itempk=2

SELECT s.* 
FROM Suppliers s
WHERE 
    EXISTS (SELECT 1 FROM SupplierItems si1 WHERE si1.supplierpk = s.id and si1.itempk = 1) 
    AND
    EXISTS (SELECT 1 FROM SupplierItems si2 WHERE si2.supplierpk = s.id and si2.itempk = 2)