MySQL query WHERE NOT EQUAL not working

Question

I have 3 tables in my DB: recipes, ingredients and recipe_ingredients. Recipes and ingredients are connected with many-to-many, so the table recipe_ingredients is a junction table. I want to select all recipes from table recipes, which does not include ingredient with id X. And it's still returns all the recipes. If I write a query to return recipes with ingredient X, it works. Here's my query:

select * from recipes
join recipe_ingredients ON recipes.id = recipe_ingredients.fk_Recipe
join ingredients ON recipe_ingredients.fk_Ingredient = ingredients.id
where recipe_ingredients.fk_Ingredient != 307
GROUP by recipes.url  

Answer 1

Any recipe that as an ingredient other than 307 will satisfy the condition.

That is, to this query, it doesn't matter if one of the ingredients is 307, as long as the recipe has some other ingredient other than 307, it's a match.

To get recipes that do not have 307 as an ingredient, we can use an anti-join or a NOT EXISTS

---

anti-join pattern

 SELECT r.id
      , r.url
   FROM recipes r
   LEFT
   JOIN recipe_ingredients s
     ON s.fk_recipe     = r.id
    AND s.fk_ingredient = 307
  WHERE s.fk_ingredient IS NULL

-or-

not exists pattern

 SELECT r.id
      , r.url
   FROM recipes r
  WHERE NOT EXISTS 
        ( SELECT 1
            FROM recipe_ingredients s
           WHERE s.fk_recipe     = r.id
             AND s.fk_ingredient = 307
        )

FOLLOWUP

To return recipes that do not have 307 as an ingredient, but do have 42 as an ingredient...

 SELECT r.id
      , r.url
   FROM recipes r
   JOIN recipe_ingredients t
     ON t.fk_recipie    = r.id
    AND t.fk_ingredient = 42 
   LEFT
   JOIN recipe_ingredients s
     ON s.fk_recipe     = r.id
    AND s.fk_ingredient = 307
  WHERE s.fk_ingredient IS NULL

-or-

 SELECT r.id
      , r.url
   FROM recipes r
  WHERE NOT EXISTS
        ( SELECT 1
            FROM recipe_ingredients s1
           WHERE s1.fk_recipe     = r.id
             AND s1.fk_ingredient = 307
        )
    AND EXISTS 
        ( SELECT 1
            FROM recipe_ingredients s2
           WHERE s2.fk_recipe     = r.id
             AND s2.fk_ingredient = 42
        )

Answer 2

The problem is that you will still include a recipe if it has at least one ingredient that is not 307, as that ingredient will pass the where condition.

You could use a having condition, like this:

select   recipes.url 
from     recipes
    join recipe_ingredients on recipes.id = recipe_ingredients.fk_Recipe
    join ingredients        on ingredients.id = recipe_ingredients.fk_Ingredient
group by recipes.url 
having   not(sum(recipe_ingredients.fk_Ingredient = 307))

When using this in combination with "positive" conditions, i.e. where a certain ingredient must be used, then keep using the same pattern, but this time without not:

select   recipes.url 
from     recipes
    join recipe_ingredients on recipes.id = recipe_ingredients.fk_Recipe
    join ingredients        on ingredients.id = recipe_ingredients.fk_Ingredient
group by recipes.url 
having   not(sum(recipe_ingredients.fk_Ingredient = 307))
   and   sum(recipe_ingredients.fk_Ingredient = 1105)

When on top of that you have conditions on the number of ingredients, then add a count condition in the having clause, for instance:

select   recipes.url 
from     recipes
    join recipe_ingredients on recipes.id = recipe_ingredients.fk_Recipe
    join ingredients        on ingredients.id = recipe_ingredients.fk_Ingredient
group by recipes.url 
having   not(sum(recipe_ingredients.fk_Ingredient = 307))
   and   sum(recipe_ingredients.fk_Ingredient = 1105)
   and   count(*) = 2

This method should be very efficient: it does not require to perform a sub query or to join the same table more than once.