dimanche 22 novembre 2015

SQL(ite) filtering one-to-many tables

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