With the tables
CREATE TABLE recipeingredients(recipeid int, ingredientid int);
CREATE TABLE ingredients(ingredientid int primary key, name text, perishable int);
ingredients
1 'ingA' 0
2 'ingB' 1
3 'ingC' 1
4 'ingD' 0
recipeingredients
1 1
1 2
2 1
2 2
2 3
3 2
3 4
The recipeingredients table is for storing all the ingredients a recipe may have, and ingredients is for storing the qualities of each unique ingredient.
I was joining recipeingredients to ingredients via the query:
SELECT * FROM recipeingredients
INNER JOIN (SELECT ingredientid, text, int FROM ingredients) i
ON recipeingredients.ingid = i.ingid
because the 'perishable' data.
Now if I want to exclude all recipes that have one of a given, static list of ingredient IDs from the selection, how would I go about doing that?
I tried the intuitive selection
SELECT * FROM recipeingredients
INNER JOIN (SELECT ingredientid, name, perishable FROM ingredients
WHERE ingredientid NOT IN (1, 3)
) i
ON recipeingredients.ingid = i.ingid
which does not work, obviously.
That is, this is the sort of selection I want to be returned, assuming the forbidden ingredient ids are 1 and 3:
recid|ingid| name |perish
3 2 'ingB' 1
3 4 'ingD' 0
since recipes 1 and 2 both have either 1 or 3 in them.
How would I go about making this selection work? Would I need a new table of ingredients to recipe IDs to filter against?
Aucun commentaire:
Enregistrer un commentaire