Getting unexpected results from SQL query

Question

So I have 2 tables. 1 called "recipes" and one called "ingredients". In the recipes table I have: id, recipe_name, calories, bld (an enumerated list that can be B, L, or D for breakfast, lunch and dinner), prep (prep time), and skill (another enumerated list for beg, int, and adv).

In the ingredients table I have: recipe_id (with a foreign key constraint to the id column in the recipes table), and ingredient_name, and some other columns.

Im trying to select all of the columns from the recipes table and the ingredient_name comlumn from the ingredients table with some conditions - I want to return all recipes, even if they dont have ingredients in the ingredient table (this wouldnt really happen in practice but this is for me to learn anyway), and I want to exclude recipes if one of their ingredients includes part of a keyword the user enters (in the next example ill be using "olive" as the keyword).

Here's my SQL so far:

SELECT recipes.*, ingredients.ingredient_name
FROM recipes
LEFT OUTER JOIN ingredients
ON ingredients.recipe_id=recipes.id
WHERE ingredients.ingredient_name <> '%olive%'
ORDER BY RAND() LIMIT 7

This only returns the recipes with ingredients and is still including recipes where "olive" is part of one of the names of the ingredients.

Thanks for any help

You aren't answering your question properly. All you are doing is removing any records from ingredients that contain '%olive%' in them. You are ~not~ limiting to only recipes that have that ingredient line. As an aside, the "recipes.*" is bad practice. Even though you have to type a bit more, a complete column list you wish is a better convention. — T Gray
– T Gray, Commented Apr 18, 2016 at 16:24

JNevill · Accepted Answer · 2016-04-18 16:31:17Z

1

It's best, when working with this type of logic, to think in sets of data.

Set 1: List of recipes where Olive is in the ingredients:

SELECT recipe_id
FROM ingredients
WHERE ingredient_name LIKE '%olive%'

Now we have a set of recipes that we can exclude because they have Olive in them.

SET 2: List of recipes and their ingredients:

SELECT recipes.*, ingredients.ingredient_name
FROM recipes
LEFT OUTER JOIN ingredients
ON ingredients.recipe_id=recipes.id

No surprises here. Just a set of everything. What we want to do is restrict this set, by the set in the previous step:

SET 3: Restrict SET2 set with SET1 set:

SELECT recipes.*, ingredients.ingredient_name
FROM recipes
LEFT OUTER JOIN ingredients
ON ingredients.recipe_id=recipes.id
WHERE recipes.id NOT IN 
   (
     SELECT recipe_id
     FROM ingredients
     WHERE ingredient_name LIKE '%olive%'
   )

edited Apr 18, 2016 at 16:31

answered Apr 18, 2016 at 16:23

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PaulF Over a year ago

Good answer. Alternatively - WHERE recipes.id NOT IN could be replaced with WHERE NOT EXISTS in "SET 3"

HauntedSmores Over a year ago

Thanks a lot! Going to test this now

Collectives™ on Stack Overflow

Getting unexpected results from SQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related