CODEWITHSUNDEEP
mysqlsqlmany-to-many
YuriiChmil
YuriiChmil

Reputation: 481

Mysql many to many relations. Filter exact match

My schema

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 ?

Upvotes: 1

Views: 349

Answers (4)

Nguyen Tan Bao
Nguyen Tan Bao

Reputation: 111

i have a simple approach: - join 4 table and select the meal id that ingredient name not in ('potato', 'chicken') - select the meals that the id not in the set of ids found above. Here the code (SQL server):

select * from test.dbo.meal where test.dbo.meal.id not in (
  select m.id from test.dbo.meal m 
      inner  join test.dbo.meal_ingredient mi on m.id = mi.meal_id
      inner  join test.dbo.Ingredient i on i.id = mi.ingredient_id
      inner  join test.dbo.Ingredient_alias ia on ia.id = i.Ingredient_alias_id
  where ia.name not in ('potato', 'chicken')
)

Upvotes: 0

forpas
forpas

Reputation: 164099

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 |

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You are dealing with ingredient categories that you call ingredient aliases. E.g. you have the category 'rice' which can be 'white rice', 'red rice', and whatever other rice. You are looking for meals that consist exactly of a group of given ingredient categories, e.g. 'rice' and 'chicken'. In the worst case your meal contains a category multiple times. Lets say a meal consists of 'chicken' and 'white rice' and 'red rice'. In spite of having three ingredients, this meal consists of only two ingredient categories.

As of MySQL 8:

with ia as
(
  select id from ingredient_alias where name in ('potato', 'chicken')
)
select *
from meal
where id in
(
  select mi.meal_id
  from meal_ingredient mi
  join ingredient i on i.id = mi.ingredient_id
  left join ia on ia.id = i.alias_id
  group by mi.meal_id
  having count(distinct ia.id) = count(distinct i.alias_id)
     and count(distinct ia.id) = (select count(*) from ia)
);

For an older version remove the WITH clause and write the left join and the subquery without it (or count yourself and replace the subquery with that number).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Here is one method:

select m.meal_id
from meal_ingredient m join
     ingredient i
     on m.ingredient_id = i.id left join
     ingredient_alias ia
     on ia.id = i.ingredient_alias_id
group by m.meal_id
having count(ia.id) = count(*) and                           -- all ingredients in meal match list
       count(*) = (select count(*) from ingredient_alias)    -- all ingredients in list are present

If you can have duplicates, the second condition should be count(distinct m.ingredient_id) instead of count(*).

Upvotes: 0

Related Questions