MySQL query with multiple joins returning multiple duplicate results

Question

I am trying to query 4 tables.

Here are the tables:

recipes - contains all recipes

recipe_steps - the cooking steps on the recipes (1 to m)

ingredients_on_recipes - a m to m joining table that contains the recipe id and the ingredient id (m to m)

ingredients - contains all ingredients (1 to m)

The issue I am having is I am getting 65 rows returned, when I should only be getting 18.

Here is my MySQL query:

SELECT r.id, rs.id, i.id FROM recipes r
  JOIN recipe_steps rs ON rs.recipe_id = r.id
  JOIN ingredients_on_recipes ior ON ior.recipeId = r.id
  JOIN ingredients i ON i.id = ior.ingredientId
WHERE r.id = 19

When I use the following query:

SELECT r.id, rs.id FROM recipes r
  JOIN recipe_steps rs ON rs.recipe_id = r.id
WHERE r.id = 19

I get 5 rows returned as expected.

When I use the following query:

SELECT r.id, i.id FROM recipes r
  JOIN ingredients_on_recipes ior ON ior.recipeId = r.id
  JOIN ingredients i ON i.id = ior.ingredientId
WHERE r.id = 19

I get 13 rows returned as expected.

But when I use both recipe_steps and ingredients in one query I get 65 rows with many duplicates on the ingredients and steps.

I have also tried adding:

GROUP BY r.id, rs.id, i.id

But again this returns 65 rows, instead of 18.

Looking at the rows being returned, what seems to be happening is the steps (1,2,3,4,5) are returning 1 for all 13 ingredients rows, then 2 and repeating the 13 ingredients rows and so on up to the 5th step.

Hi @TimBiegeleisen, ok thanks, I will add it the data. What seems to be happening is the steps (which should be 1,2,3,4,5) is showing 1 for all 13 ingredients, then 2 for all 13 ingredients and so on. Do you have any idea why this could be? — herbie
– herbie, Commented Apr 10, 2022 at 8:21
If you have one recipe with 10 steps it will return the recipe 10 times with the steps, You have to break up the query, first get the recipes then for each recipe, get the steps. — Grumpy
– Grumpy, Commented Apr 10, 2022 at 8:32
Thanks @Grumpy, so what your saying is I have to do 3 seperate queries? One to get the recipe, one to get the steps and then one to get the ingredients? — herbie
– herbie, Commented Apr 10, 2022 at 8:38
@herbie Thats correct, first query the Recipes, then go through the results and ad the ingredients and the steps. — Grumpy
– Grumpy, Commented Apr 10, 2022 at 9:16

Luuk · Accepted Answer · 2022-04-10 09:02:34Z

2

You have a cartesian product

When you have 3 recipe_steps, and 3 ingredients, and you try to select 1 recipe, you will end up with 9 rows. This is because every recipe_step is also joined to an ingredient.

I created a small DBFIDDLE to show this.

The queries in the fiddle (which assume, in this example that you have 3 steps per recipe, and 3 ingredients per recipe):

-- This will return 3 rows, because the are 3 recipe_steps for recipe=1
SELECT *
FROM recipes r
INNER JOIN recipe_steps rs on rs.recipe_id = r.id
WHERE r.id=1

-- This will return 9 rows, 
--     because the are 3 recipe_steps for recipe=1
--     and 3 ingredients for recipe=1
--     and 3 * 3 = 9
SELECT *
FROM recipes r
INNER JOIN recipe_steps rs on rs.recipe_id = r.id
INNER JOIN ingredients_on_recipes ir on ir.recipe_id = r.id
WHERE r.id=1

This behavior can only be solve by creating a (1-1) relation between recipe_steps and ingredients, and then adding this relationship to the ON-clause of the query.

answered Apr 10, 2022 at 9:02

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

herbie Over a year ago

Thanks @Luuk, appreciate the answer. I understand now, unfortunately a 1-1 doesn't exist there. So I will have to do the queries separately.

Collectives™ on Stack Overflow

MySQL query with multiple joins returning multiple duplicate results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related