CODEWITHSUNDEEP
sqlsqlite
Sky
Sky

Reputation: 220

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, name, perishable FROM ingredients) i
ON recipeingredients.ingredientid = i.ingredientid

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.ingredientid = i.ingredientid

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:

recipeid|ingredientid| name |perishable  
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?

Upvotes: 1

Views: 44

Answers (1)

CL.
CL.

Reputation: 180020

You do not need to use a subquery to do a join, and joins can be simplified with a USING clause if both column names match:

SELECT recipeid,
       ingredientid,
       name,
       perishable
FROM recipeingredients
JOIN ingredients USING (ingredientid);

Anyway, the WHERE clause on the SELECT ... FROM ingredients subquery returns all ingredients not on your list.

If you want to exclude all recipes that have one of a given, static list of ingredient IDs, you have to write that in SQL.

This query would select all recipe IDs that have one of a given, static list of ingredient IDs:

SELECT DISTINCT recipeid
FROM recipeingredients
WHERE ingredientid IN (1, 3);

And this query then excludes those recipes from the result:

SELECT recipeid,
       ingredientid,
       name,
       perishable
FROM recipeingredients
JOIN ingredients USING (ingredientid)
WHERE recipeid NOT IN (SELECT recipeid
                       FROM recipeingredients
                       WHERE ingredientid IN (1, 3));

Upvotes: 1

Related Questions