Muralidhar Yaragalla Muralidhar Yaragalla -5 years ago 84
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 Source

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

FROM Suppliers s
    EXISTS (SELECT 1 FROM SupplierItems si1 WHERE si1.supplierpk = and si1.itempk = 1) 
    EXISTS (SELECT 1 FROM SupplierItems si2 WHERE si2.supplierpk = and si2.itempk = 2) 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download