CODEWITHSUNDEEP
mysqljoingroup-by
vittorio
vittorio

Reputation: 181

Grouping results after join and having clause

Let's say I have three tables (mysql):

recipes

+----+----------------+--------------+
| id |     title      |   image      |
+----+----------------+--------------+
|  2 | recipe title 1 | banana image |
|  3 | recipe title 2 | potato image |
+----+----------------+--------------+

ingredient

+----+-----------+---------+---------------+
| id | recipe_id | food_id | quantity_kg   |
+----+-----------+---------+---------------+
|  1 |         2 |      36 | 2.5           |
|  2 |         3 |      37 | 1.5           |
+----+-----------+---------+---------------+

food

+----+---------+-------+-----------+----------+
| id |  name   | price | foodType  | unitType |
+----+---------+-------+-----------+----------+
| 36 | carrot  |     2 | vegetable | kg       |
| 37 | chicken |    12 | meat      | kg       |
+----+---------+-------+-----------+----------+

Now, I want to get all the recipes that are vegetarian, i.e. that don't contain any foods where foodType is 'meat' (or other animal product).

How do I perform such query?

Here is what I've tried so far:

SELECT
  recipe.id as recipeId,
  recipe.title as title,
  food.type as foodType
FROM recipe
INNER JOIN ingredient on ingredient.recipe_id = recipe.id
INNER JOIN food on food.id = ingredient.aliment_id
HAVING 
  food.type <> 'meat' AND
  food.type <> 'seafood' AND
  food.type <> 'fish' 
ORDER BY recipeId

This works (I get only vegetarian recipes) BUT it duplicates all the recipes, as long as they have multiple ingredients. eg. :

+----------+--------+----------+
| recipeId | title  | foodType |
+----------+--------+----------+
|        5 | titleA | type1    |
|        5 | titleA | type2    |
|        5 | titleA | type3    |
|        8 | titleB | type2    |
|        8 | titleB | type5    |
|        8 | titleB | type1    |
|        8 | titleB | type3    |
+----------+--------+----------+

What I want to obtain is:

+----------+--------+
| recipeId | title  |
+----------+--------+
|        5 | titleA |
|        8 | titleB |
+----------+--------+

I already tried getting rid of 'foodType' in the SELECT clause, but if I do so, mysql tells me : "Unknown column 'food.type' in 'having clause'"

I already tried to GROUP BY 'recipeId' right before HAVING clause, but I get that error : "Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'myDb.food.type' which is not functionally dependent on columns in GROUP BY clause" (I understand that error).

I guess it has to do with something like "Grouping results after join and having clause", but I might be wrong...

Thanks a lot

Upvotes: 1

Views: 56

Answers (2)

Dmitry Reutov
Dmitry Reutov

Reputation: 3032

just exclude all recipes which have at least one meat ingridient (i did it 10 years ago even without sql)

SELECT recipe.id, recipe.title FROM recipe
WHERE recipe.id NOT IN (
   SELECT
      recipe.id,
   FROM recipe
   INNER JOIN ingredient 
      on ingredient.recipe_id = recipe.id
   INNER JOIN food 
      on food.id = ingredient.aliment_id
      AND food.type IN ('meat', 'seafood', 'fish') 
)
ORDER BY recipeId

Upvotes: 1

alttag
alttag

Reputation: 1173

  • You don't have a GROUP BY clause, so you shouldn't have a HAVING clause. Use WHERE instead
  • Remove the unwanted columns from your SELECT
  • Because the joins are across 1:many relationships but you're only selecting on the "one" side, you probably also want SELECT DISTINT instead of just SELECT

Also, you have another issue: The logic of your query isn't actually correct, even though it's returning apparently correct results with such a small set of sample data.

When looking at composition, you probably want to use EXISTS and a subquery. Perhaps something like this (untested):

SELECT
  recipe.id as recipeId,
  recipe.title as title,
  food.type as foodType
FROM recipe r
WHERE NOT EXISTS
  (SELECT food.type
   FROM ingredient INNER JOIN food on food.id = ingredient.aliment_id
   WHERE ingredient.recipe_id = r.id AND 
      food.type IN ('meat', 'seafood','fish')
  )
ORDER BY recipeId

Upvotes: 1

Related Questions