Mysql select with multiple tables and multiple foreign keys

Question

If I have three tables like:

suppliers (sID: integer, sName: varchar, description: varchar); With sID as primary key

+-----+-----------+-------------+
| sID |   SName   | description |
+-----+-----------+-------------+
| 1   | Supplier1 | Desc1       |
| 2   | Supplier2 | Desc2       |
| 3   | Supplier3 | Desc3       |
| 4   | Supplier4 | Desc4       |
| ... | ...       | ...         |
+-----+-----------+-------------+

products (pID: integer, pName: varchar, size: varchar); With pID as primary key

+-----+----------+------+
| pID |  pName   | size |
+-----+----------+------+
| 1   | Product1 | S    |
| 2   | Product2 | M    |
| 3   | Product3 | B    |
| 4   | Product4 | M    |
| ... | ...      | ...  |
+-----+----------+------+

menu (sID: integer, pID: integer, cost: varchar); with sID and pID as foreign keys

+-----+-----+------+
| sID | pId | cost |
+-----+-----+------+
| 1   | 4   | 10   |
| 2   | 16  | 20   |
| 8   | 1   | 5    |
| 8   | 2   | 8    |
| ... | ... | ...  |
+-----+-----+------+

The only relation between suppliers and products is given by the menu table.

How can I select the names of all suppliers that supply all products?

Thanks for any help!

Answer 1

Here is how you would get the ids. Simply aggregate and count:

select m.sid
from menu m
group by m.sid
having count(distinct pid) = (select count(*) from products);

I'll leave it to you to get the supplier name, which you can do using in, exists, join, or even a correlated subquery.