Muralidhar Yaragalla Muralidhar Yaragalla - 4 months ago 6x
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.


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)