MySQL query with multiple joins returning multiple duplicate results-CodePudding

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.

CodePudding user response：

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.