Mysql many to many relations. Filter exact match

Hello. I want to select dishes which has the only the exactly selected ingredient alias? For example: Ingredient alias table:

+----+----------+
| id |   Name   |
+----+----------+
| 22 |  potato  |
| 23 |  rice    |
| 29 |  chicken |
+----+----------+

Ingredient table:

+------+-----------+----------+-------+------+----------+
|  id  |   name    | proteins | carbs | fats | alias_id |
+------+-----------+----------+-------+------+----------+
| 3043 | Chicken 1 | 44.0     | 3.0   | 3.0  |       29 |
| 3025 | Rice 1    | 44.0     | 32.0  | 23   |       23 |
| 3024 | Rice 2    | 23.0     | 22.0  | 33.0 |       23 |
| 3042 | Chicken 2 | 22.0     | 22.0  | 3.0  |       29 |
| 3022 | Potato 1  | 22.0     | 22.0  | 32.0 |       22 |
| 3021 | Potato 2  | 20.0     | 30.0  | 40.0 |       22 |
| 3041 | Chicken 3 | 11.0     | 11.0  | 11.0 |       29 |
| 3026 | Rice 3    | 1.0      | 1.0   | 1.0  |       23 |
| 3023 | Potato 3  | 1.0      | 2.0   | 3.0  |       22 |
+------+-----------+----------+-------+------+----------+

Table Meal:

+----+-----------------------------------------+
| id |                  name                   |
+----+-----------------------------------------+
|  1 | Meal with Chicken 1 and Rice 1          |
|  2 | Meal with Chicken 1 and Rice 2          |
|  3 | Meal with Chicken 2 Potato 1            |
|  4 | Meal with Chicken 2 Potato 1 and Rice 1 |
+----+-----------------------------------------+

Table meal_ingredient:

+-------+---------+---------------+--------+------+
|  id   | meal_id | ingredient_id | weight | role |
+-------+---------+---------------+--------+------+
| 13366 |       1 |          3043 |     13 |    1 |
| 13367 |       1 |          3025 |      1 |    1 |
| 13368 |       2 |          3043 |     12 |    2 |
| 13369 |       2 |          3024 |      8 |    3 |
| 13370 |       3 |          3042 |     22 |    1 |
| 13371 |       3 |          3022 |      1 |    1 |
| 13372 |       4 |          3042 |      3 |    1 |
| 13373 |       4 |          3022 |      3 |    3 |
| 13374 |       4 |          3024 |      2 |    2 |
+-------+---------+---------------+--------+------+

How I can get meals only which has ingredients only with ingredient aliases Potato and chicken? In my example, the result must be a meal with id 3 Meal with Chicken 2 Potato 1 ?

Answer

You need to join all 4 tables, group by meal and put the condition in the HAVING clause:

select m.id, m.name
from ingredient_alias ia
inner join ingredient i on i.alias_id = ia.id
inner join meal_ingredient mi on mi.ingredient_id = i.id
inner join meal m on m.id = mi.meal_id
group by m.id, m.name
having 
  count(distinct ia.name) = 2
  and 
  sum(ia.name not in ('potato', 'chicken')) = 0

See the demo.
Results:

| id  | name                             |
| --- | -------------------------------- |
| 3   | Meal with Chicken 2 and Potato 1 |

Advertisement

Answer