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

JNevill JNevill · Accepted Answer · 2016-04-18T16:23:57

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%'
   )

Getting unexpected results from SQL query

1 Answers