Muralidhar Yaragalla Muralidhar Yaragalla - 1 year ago 67
MySQL Question

how to get only the suppliers who supplies all the required items?

hi i have a table which shows the relation between supplier and Items. the following is the table:-

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

Now i want to get only the suppliers who supplies item 1 and 2(only suppliers that supply both the items). how to write sql query for this? i am using mysql database.

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