sql group by – curse and blessing

I have 3 tables for a meal calculation.

CREATE TABLE Meals (
    meal_id int,
    name text
);

CREATE TABLE Ingredients (
    ingredient_id int,
    name text,
    minamount float,
    price float
);

CREATE TABLE Recipe (
    meal_id int,
    ingredient_id int,
    quantitiy float,
    ingredientName text
);

Now I like to find a meal by ingredient:

SELECT m.meal_id, 
                    m.name,
                    r.ingredientName
                    from meals m
                    join recipe r on m.meal_id = r.meal_id
                    join ingredients i on i.ingredient_id = r.ingredient_id
                    where r.ingredientName = 'meat'
                    group by m.meal_id

Which give me a list like:

name, ingredientName
Lasagne, meat
Bolognese, meat

Also I like to find meal by price like:

SELECT m.meal_id, 
                    m.name,
                    SUM(i.price / i.minamount * r.quantity) as mealPrice
                    from meals m
                    join recipe r on m.meal_id = r.meal_id
                    join ingredients i on i.ingredient_id = r.ingredient_id
                    GROUP BY m.meal_id, 
                             m.name
                    HAVING mealPrice > 5.0 AND mealPrice < 8.0 

Which give me a list like:

name, mealPrice
Lasagne, 6.5
Bolognese, 7.8

Now let me build a query like

SELECT m.meal_id, 
                    m.name,
                    r.ingredientName,
                    SUM(i.price / i.minamount * r.quantity) as mealPrice
                    from meals m
                    join recipe r on m.meal_id = r.meal_id
                    join ingredients i on i.ingredient_id = r.ingredient_id
                    where r.ingredientName = 'meat'
                    GROUP BY m.meal_id
                             r.ingredientName  
                    HAVING mealPrice > 5.0 AND mealPrice < 8.0

Which gives me a list like:

name, ingredientName, mealPrice
Lasagne, meat, 1.99
Bolognese, meat, 1.99

Because (i.price / i.minamount * r.quanitity) for this meal for meat is 1.99

Without “GROUP BY r.ingredientName” it returns only one values

name, ingredientName, mealPrice
Lasagne, meat, 6.5

But I need a list like:

name, ingredientName, mealPrice
Lasagne, meat, 6.5
Bolognese, meat, 7.8

Answer

Perhaps add an EXISTS to only get the meals with ‘meat’ in the recipe.

SELECT 
 m.meal_id, m.name,
 SUM((i.price / i.minamount) * r.quantity) as mealPrice
FROM meals m
JOIN recipe r ON r.meal_id = m.meal_id
JOIN ingredients i ON i.ingredient_id = r.ingredient_id
WHERE EXISTS
(
  SELECT 1
  FROM recipe r2
  WHERE r2.ingredientName = 'meat'
    AND r2.meal_id = m.meal_id
)
GROUP BY m.meal_id, m.name
HAVING mealPrice BETWEEN 5.01 AND 7.99