0

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
Improve this question
9
  • I would advise to use the SQL standard <> operator instead of !=. Commented May 23, 2018 at 15:43
  • 1
    This also smells of a typical broken MySQL Group By, which I consider a serious design flaw. Surely there are more than 1 column in your select, and there's no aggregate function to be found. Commented May 23, 2018 at 15:44
  • <> doesn't change anything. If I don't put GROUP By, the result are recipes x ingredients they have. Maybe it's missing something Commented May 23, 2018 at 15:45
  • 1
    Why was this downvoted? It's flawed SQL but not a bad question. Rather than just downvote, how about explaining why you thought the question from a new user deserved the downvote. Commented May 23, 2018 at 16:16
  • 1
    @AirwaveQ: A belated welcome from the Stack Overflow community! We're glad you're here. I ask that you please excuse our behavior (downvoting your question), and I encourage you to not be discouraged by the downvoters. Commented May 23, 2018 at 20:32

2 Answers 2

Reset to default
2

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
        )
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Yes, this one works fine! Thank you. Now I should somehow put this in eloquent ;)
I'll throw in a vote for the anti-join pattern. It usually performs very well, especially on large datasets.
Also note that EXISTS is a RBAR [sqlservercentral.com/Forums/Topic642789-338-1.aspx] operation, so it can impact performance.
@Shawn: my preference is the anti-join pattern, which is why I gave that first. But that anti-join pattern can be difficult for folks to get their brains wrapped around when they first encounter it.... the NOT EXISTS pattern seems much easier to grasp. (Curiously, the EXPLAIN for the anti-join will show "Not exists" in the Extra column, where that doesn't show for the NOT EXISTS pattern.) In terms of performance, my experience is that the two are nearly equivalent; I think MySQL (>=5.6) doesn't perform separate subquery executions, i.e. the SQL looks like it's RBAR but it's not really.
@spencer7593 Gotcha. It may behave differently in MySQL vs MS T-SQL. MySQL may do a better job of optimizing or figuring out what you really meant. A NOT EXISTS would usually perform well since it bails out at the first match (MUCH better than a NOT IN), so if your row matches early it'll return quickly. But I'm still not sure it's set-based, whereas the anti-join is.
|
1

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.

Improve this answer

6 Comments

In this case, it works. But if I put where, it doesn't work anymore. For example, I need to find recipes, which has ingredient X, but doesn't have ingredient Y. 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 = 1105 group by recipes.url having sum(recipe_ingredients.fk_Ingredient = 307) = 0
to note a corner case... this query won't return a recipe that has no ingredients. This requires a recipe have at least one ingredient....
Honestly I would like to find a recipe without ingredients. It would make my cooking so much cheaper. But on a serious note, just wrap the sum in coalesce(..., 0) for that corner case.
Instead of using a where clause for a positive test, use the same pattern again within tha having clause, but with >0
As I mentioned, it's a corner case, it's so remote, that it doesn't even qualify as an edge case. You're right, a "recipe" without "ingredients" doesn't make much sense But this query does satisfy a slightly different specification ... "recipes with at least one ingredient that does not have 307 as an ingredient." In the more general case, that distinction can be important.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.