Should I find correct MySQL query or optimize database structure?

Question

I have database of following structure:

TABLE ingredients (ingredient_name, color)
TABLE recipes (recipe_name)
TABLE recipes_ingredients_parts (recipe_name, ingredient_name, parts)

What I want is to get a recipe that corresponds with selected ingredients and their number. So what I`ve tried first was query:

SELECT rr.* FROM 
  (SELECT r.* FROM receipes r 
    INNER JOIN receipes_ingredients_parts ri 
      ON r.receipe_name = ri.receipe_name 
        AND ri.ingredient_name = 'espresso' 
        AND ri.parts_number = '1') rr;

And what I get are {"Americano", "Espresso"}. But that should be "Espresso" only because for "Americano" there should be the query:

SELECT rr.* FROM 
 (SELECT r.* FROM receipes r 
   INNER JOIN receipes_ingredients_parts ri 
     ON r.receipe_name = ri.receipe_name 
       AND ri.ingredient_name = 'espresso' 
       AND ri.parts_number = '1') rr 
   INNER JOIN receipes_ingredients_parts ri 
     ON rr.receipe_name = ri.receipe_name 
       AND ri.ingredient_name = 'water' 
       AND ri.parts_number = '4';

Next my idea was to alter recipe table and add columns for each ingredient to store it's quantity for the recipe. But it would be near 20 columns of that kind. So I'm confused with thought that I'm doing job in a bad style. Maybe I should use some good query for the purpose? Do you guys have any ideas about all the stuff?

Adding 20 columns would not be "optimizing" your db structure. Your first query is getting any recipe that has "espresso" as an ingredient; did you want it to get recipes that have "espresso" as the only ingredient? (If so, this might be the answer you're looking for.) — Uueerdo
Oh, yeah, I guess that is not quite right either; it gives you things that have all items in the list, not the things have only the items in the list. — Uueerdo

Uueerdo Uueerdo · Accepted Answer · 2016-12-12T19:04:23

I think this is what you are looking for, it should find receipe_names that have all the ingredients in your list, and no other ingredients.

SELECT receipe_name
, SUM(CASE 
      WHEN (ingredient_name, parts_number) IN (('espresso','1')) 
      THEN 1 ELSE 0 
      END
) AS matchedIngredients
, SUM(CASE 
      WHEN (ingredient_name, parts_number) NOT IN (('espresso','1')) 
      THEN 1 ELSE 0 
      END
) AS otherIngredients
FROM receipes_ingredients_parts
GROUP BY receipe_name 
HAVING matchedIngredients = 1 AND otherIngredients = 0

A more generalized version/template:

SELECT aField
, SUM(CASE 
      WHEN someField IN ([matchList]) 
      THEN 1 
      ELSE 0 
      END
) AS matches
, SUM(CASE 
      WHEN someField NOT IN ([matchList]) 
      THEN 1 
      ELSE 0 
      END
) AS others
FROM aTable
GROUP BY aField
HAVING matches = [# of values in matchlist] 
   AND others = 0

Alternatively, if items in the matchlist might be repeated in the table for an "aField" value:

SELECT aField
, COUNT(DISTINCT CASE 
      WHEN someField IN ([matchList]) 
      THEN someField
      ELSE NULL
      END
) AS matches
, COUNT(DISTINCT CASE 
      WHEN someField NOT IN ([matchList]) 
      THEN someField 
      ELSE NULL
      END
) AS others
FROM aTable
GROUP BY aField
HAVING matches = [# of values in matchlist] 
   AND others = 0

Should I find correct MySQL query or optimize database structure?

1 Answers