CODEWITHSUNDEEP
mysqljoinwhere-clause
Fernando Penteado
Fernando Penteado

Reputation: 101

SQL Query Unclear

I am trying to create a query that returns a specific set of registers, but i tried several ways and it is not working.

I have 4 tables like below:

recipe {
    idRecipe integer,
    name varchar(100)
}

recipeStep {
    idStep integer,
    idRecipe integer,
    instruction varchar(100)
}

recipeIngredient {
    idStep integer,
    idIngredient integer
}

ingredient {
    idIngredient integer,
    name varchar(100)
}

The filter is to return all recipe that contain the ingredients selected by the user. Lets say i want all recipes that contains all recipes connotation the ingredients 68,25,36,109. So I came to this:

SELECT r.* 
  FROM recipe r, recipeStep rs, recipeIngredient ri 
 WHERE r.idRecipe = rs.idRecipe
   AND rs.idStep = ri.idStep
   AND ri.idIngredient in (68,25,36,109)

The problem is that this query returns ALL recipes that contains ANY of those ingredients and I want only ALL recipes that contains ALL of them

Upvotes: 0

Views: 85

Answers (1)

jpw
jpw

Reputation: 44901

One way to do this is to use a group by clause together with a having clause that limits the groups to the ones that have a count of 4 distinct ingredients.

SELECT r.idRecipe, r.name
  FROM recipe r
  JOIN recipeStep rs ON r.idRecipe = rs.idRecipe
  JOIN recipeIngredient ri ON rs.idStep = ri.idStep
 WHERE ri.idIngredient IN (68,25,36,109)
GROUP BY r.idRecipe, r.name
HAVING COUNT(DISTINCT ri.idIngredient) = 4;

Alternatively you could skip the IN predicate and use conditional aggregation in the having clause:

SELECT r.idRecipe, r.name
  FROM recipe r
  JOIN recipeStep rs ON r.idRecipe = rs.idRecipe
  JOIN recipeIngredient ri ON rs.idStep = ri.idStep
GROUP BY r.idRecipe, r.name
HAVING SUM(CASE WHEN ri.idIngredient = 68  THEN 1 END) > 0
   AND SUM(CASE WHEN ri.idIngredient = 25  THEN 1 END) > 0
   AND SUM(CASE WHEN ri.idIngredient = 36  THEN 1 END) > 0
   AND SUM(CASE WHEN ri.idIngredient = 109 THEN 1 END) > 0;

Both queries would allow additional ingredients, as long as the recipe has the four mandatory ones. If you want exact matches you can just add another condition to the where clause.

Upvotes: 2

Related Questions