Selecting ALL records when condition is met for ALL

Question

I'm having a bit of trouble with getting a select statement to work correctly.

I have the following tables:

recipe	recipe_ingredient	pantry
recipe1	ingredient1	Ingredient1
recipe1	ingredient2	Ingredient2
recipe2	ingredient3

I want a select statement that returns recipes when the user's pantry has all ingredients for that recipe.

if ($res = $mysqli->query('
SELECT * 
FROM recipe 
WHERE NOT EXISTS (
    SELECT recipe_id 
    FROM recipe_ingredient 
    WHERE NOT EXISTS (
        SELECT ingredient_id 
        FROM pantry 
        WHERE recipe_ingredient.ingredient_id = pantry.ingredient_id 
          AND iduser = ' . $_COOKIE["idUser"] . '
    )
)
')) {

My code works when only one recipe in the db but when muliple recipes are added the code no longer works. Using the above table as an example it should return recipe1 but it returns nothing unless the pantry has ingredient3 then both recipes are returned.

How do I amend my select statement to fix this?

score 2 · Answer 1 · answered Mar 11 '23 at 22:48

You want recipes that your user has all ingredients for, which reads like a relational division problem.

Starting from the tables and columns names of your original query, here is a query that gives you the recipe ids:

select ri.recipe_id
from recipe_ingredient ri 
inner join pantry p on p.ingredient_id = ri.ingredient_id
where p.iduser = ?
group by ri.recipe_id
having count(*) = ( 
    select count(*) 
    from recipe_ingredient ri1 
    where ri1.recipe_id = r.recipe_id
)

The idea is to filter the recipe ingredient table for ingredients that the user has, and then ensure that none misses in the same recipe, using the having clause.

Side note: do not concatenate parameters as strings in your query, as it opens up your application to SQL injection; using bind parameters instead.

score 0 · Accepted Answer · answered Mar 11 '23 at 23:00

you can do following correlated subquery with exists, which will test if all ingredients are filled up, which woul be when all recipe_ingredient ingridents_id have a value that is Not NULL

But you should be aware that your code is vulnerable to sql injection and so you should only use prepared statements with parameters see How can I prevent SQL injection in PHP? for more information

SELECT * from recipe r
WHERE NOT EXISTS (SELECT 
                    1 
                FROM recipe_ingredient rp LEFT JOIN pantry ON rp.ingredient_id 
                = p.ingredient_id 
                AND p.iduser= ? 
                WHERE rp.recipe_id = r.recipe_id AND p.ingredient_id IS NULL)

Selecting ALL records when condition is met for ALL

2 Answers2