mysql - Find all rows in one table that are in two other tables -

i'm struggling how query this. have 3 tables...ingredients, recipes, , stores. i'm trying build query tell me recipes can make ingredients available @ store. tables are:

      mysql> select * ingredients;     +---------+     | id      |     +---------+     | apple   |     | beef    |     | cheese  |     | chicken |     | eggs    |     | flour   |     | milk    |     | pasta   |     | sugar   |     | tomato  |     +---------+     10 rows in set (0.00 sec)      mysql> select * stores;     +----------+------------+     | name     | ingredient |     +----------+------------+     | target   | apple      |     | target   | chicken    |     | target   | flour      |     | target   | milk       |     | target   | sugar      |     | wal-mart | beef       |     | wal-mart | cheese     |     | wal-mart | flour      |     | wal-mart | milk       |     | wal-mart | pasta      |     | wal-mart | tomato     |     +----------+------------+     11 rows in set (0.00 sec)      mysql> select * recipes;     +---------------+------------+     | name          | ingredient |     +---------------+------------+     | apple pie     | apple      |     | apple pie     | flour      |     | apple pie     | milk       |     | apple pie     | sugar      |     | cheeseburger  | beef       |     | cheeseburger  | cheese     |     | cheeseburger  | flour      |     | cheeseburger  | milk       |     | fried chicken | chicken    |     | fried chicken | flour      |     | spaghetti     | beef       |     | spaghetti     | pasta      |     | spaghetti     | tomato     |     +---------------+------------+     13 rows in set (0.00 sec)      mysql>  

given above want build query give store name (say wal-mart example) , produces list of recipes can make ingredients available @ wal-mart (cheeseburger & spaghetti).

here's sql create these tables:

      create table if not exists ingredients (       id varchar(32) not null,       primary key (id)     ) engine=innodb default charset=latin1;      insert ingredients (id) values     ('apple'),     ('beef'),     ('cheese'),     ('chicken'),     ('eggs'),     ('flour'),     ('milk'),     ('pasta'),     ('sugar'),     ('tomato');      create table if not exists recipes (       `name` varchar(32) not null,       ingredient varchar(32) not null,       primary key (`name`,ingredient)     ) engine=innodb default charset=latin1;      insert recipes (`name`, ingredient) values     ('apple pie', 'apple'),     ('apple pie', 'flour'),     ('apple pie', 'milk'),     ('apple pie', 'sugar'),     ('cheeseburger', 'beef'),     ('cheeseburger', 'cheese'),     ('cheeseburger', 'flour'),     ('cheeseburger', 'milk'),     ('fried chicken', 'chicken'),     ('fried chicken', 'flour'),     ('spaghetti', 'beef'),     ('spaghetti', 'pasta'),     ('spaghetti', 'tomato');      create table if not exists stores (       `name` varchar(32) not null,       ingredient varchar(32) not null,       unique key name_ingredient (`name`,ingredient)     ) engine=innodb default charset=latin1;      insert stores (`name`, ingredient) values     ('target', 'apple'),     ('target', 'chicken'),     ('target', 'flour'),     ('target', 'milk'),     ('target', 'sugar'),     ('wal-mart', 'beef'),     ('wal-mart', 'cheese'),     ('wal-mart', 'flour'),     ('wal-mart', 'milk'),     ('wal-mart', 'pasta'),     ('wal-mart', 'tomato');  

try this:

select r.name recipes r           group r.name having count(*) = (select count(*)       recipes r2 inner join stores s          on r2.ingredient = s.ingredient , s.name = 'wal-mart'      r.name = r2.name) 

fiddle demo