CODEWITHSUNDEEP
sqlsql-serverinner-joinwhere-clause
Nikhila Bakurupanda
Nikhila Bakurupanda

Reputation: 21

This returns nothing. Why?

SELECT 
    Recipes.RecipeID, Recipes.RecipeTitle
FROM 
    Recipes 
INNER JOIN
    Recipe_Ingredients ON Recipes.RecipeID = Recipe_Ingredients.RecipeID 
INNER JOIN
    Ingredients ON Ingredients.IngredientID = Recipe_Ingredients.IngredientID
WHERE 
   (Ingredients.IngredientName = 'Beef') 
   AND (Ingredients.IngredientName = 'Garlic')

This SQL query returns nothing. However when I checked the where conditions individually/ separately without putting them together with AND, they come up with a recipe called "Roast Beef" that actually has both beef and garlic. Hence, shouldn't it show up as 1 row in the result. But it doesn't show anything. Why?

Upvotes: 2

Views: 66

Answers (3)

Dody Suhada
Dody Suhada

Reputation: 118

you can't use AND operator for multi condition in same column, if you want to select 2 condition for same column use IN operator.

SELECT Recipes.RecipeID, Recipes.RecipeTitle
FROM Recipes INNER JOIN
Recipe_Ingredients ON Recipes.RecipeID = Recipe_Ingredients.RecipeID 
INNER JOIN
Ingredients ON Ingredients.IngredientID = Recipe_Ingredients.IngredientID
WHERE (Ingredients.IngredientName in ('Beef' , 'Garlic') )

Upvotes: 0

CodingPill
CodingPill

Reputation: 31

AND operates at row level. The IngredientName can't be both 'Beef' and 'Garlic' at the same time for the same row. You may have to join with Ingredients again.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269943

It is returning NULL, because an ingredient cannot have to names at the same time. You would seem to want:

SELECT r.RecipeID, r.RecipeTitle
FROM Recipes r INNER JOIN
     Recipe_Ingredients ri
     ON r.RecipeID = ri.RecipeID INNER JOIN
     Ingredients i
     ON i.IngredientID = ri.IngredientID
WHERE i.IngredientName IN ('Beef', 'Garlic')
GROUP BY r.RecipeID, r.RecipeTitle
HAVING COUNT(DISTINCT i.IngredientName) = 2;

Upvotes: 4

Related Questions