MySQL 'AND' query not working as expected

Question

I've got 3 tables i'm currently working on. Recipe, Ingredient and recipeIng. It's the classic many to many situation, with recipeIng there to solve that problem.

However, i'm having trouble getting a what I assumed to be, a fairly simple 'AND' query. What i'm trying to achieve is searching for multiple ingredients at once. For instance:

SELECT r.recipeTitle AS recipe
FROM Recipe r
WHERE recipeID IN(
    SELECT r.recipeID
    FROM recipeIng il, Ingredient i
    WHERE (il.ingredientID = i.ingredientID)
    AND (il.recipeID = r.recipeID)
    AND (i.ING LIKE '%cheese%' AND i.ING LIKE '%salmon%')

);

While the query runs, it outputs nothing. I can't seem to see what i'm doing wrong here. Any help would be really appreciated.

Thanks.

Your i refers in each case to the same row, so you'd only get a match if you had something like i.ing = 'salmon cheese spread'. One way to fix this is to have two ingredient tables, i_one and i_two. — halfer
– halfer, Commented Feb 7, 2013 at 14:06
you are selecting r.recipeID, I don't think alias r has 'scope' within that subquery, try changing the from clause in the second select query to FROM recipeIng il, Ingredient i, Recipe r — Paul Stanley
– Paul Stanley, Commented Feb 7, 2013 at 14:07

Ja͢ck · Accepted Answer · 2013-02-08 05:41:27Z

3

So basically you want the recipes that includes any of the ingredients you're querying on. This can be performed using simple straight joins:

SELECT DISTINCT(r.recipeTitle) AS recipe
FROM Ingredient i
INNER JOIN recipeIng il ON il.ingredientID = i.ingredientID
INNER JOIN Recipe r ON r.recipeID = il.recipeID
WHERE i.ING LIKE '%cheese%' OR i.ING LIKE '%salmon%'

The DISTINCT() makes sure that you're not seeing the same recipe twice in case it contains both ingredients.

~~If both ingredients must be present in the recipes, you must use AND instead of OR.~~

edited Feb 8, 2013 at 5:41

answered Feb 7, 2013 at 14:09

Ja͢ck

174k39 gold badges269 silver badges317 bronze badges

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

2 Comments

halfer Over a year ago

I think the two ingredients are separate, but in the same recipe? This would require the ingredient table to be aliased twice. Edit: aha, you've got an OR in there - carry on! +1

Ja͢ck Over a year ago

@halfer I'm on the fence between AND and OR here; it's up to OP to validate I guess.

Mahmoud Gamal · Accepted Answer · 2013-02-07 14:17:38Z

Use i.ING LIKE '%cheese%' OR i.ING LIKE '%salmon%' instead of AND, and add GROUP BY r.recipeID with HAVING COUNT(DISTINCT i.ING ) = 2 to ensure that the selected r.recipeID have both ING:

SELECT r.recipeTitle AS recipe
FROM Recipe r
WHERE recipeID IN(
    SELECT r.recipeID
    FROM recipeIng il
    INNER JOIN Ingredient i ON il.ingredientID = i.ingredientID 
    WHERE il.recipeID = r.recipeID
      AND (i.ING LIKE '%cheese%' OR i.ING LIKE '%salmon%')
    GROUP BY r.recipeID
    HAVING COUNT(DISTINCT i.ING ) = 2
);

SQL Fiddle Demo

So if the r.recipeID have only one of them only, then the COUNT(DISTINCT i.ING ) won't be equal to 2 and there for it will be eliminated.

The HAVING COUNT(DISTINCT i.ING ) = 2 will give you those recipes that has exactly the two tags cheese and salmon only no more ingredients, if you are looking for those recipes that has at least both two ingredients, use `HAVING COUNT(DISTINCT i.ING ) >= 2.

This is assuming that ingredients are entered in the field ING as a one value per row.

RB. · Accepted Answer · 2013-02-07 14:12:57Z

First of all, your query can be massively simplified as you do not need the outer query. The following is exactly the same:

SELECT r.recipeTitle AS recipe
FROM recipeIng il, Ingredient i, Recipe r
WHERE (il.ingredientID = i.ingredientID)
AND (il.recipeID = r.recipeID)
AND (i.ING LIKE '%cheese%' AND i.ING LIKE '%salmon%')

Secondly, you don't need all those brackets.

SELECT r.recipeTitle AS recipe
FROM recipeIng il, Ingredient i, Recipe r
WHERE il.ingredientID = i.ingredientID
AND   il.recipeID = r.recipeID
AND   i.ING LIKE '%cheese%' 
AND i.ING LIKE '%salmon%'

Thirdly, you should INNER JOIN your tables to make the relationship between them clearer:

SELECT r.recipeTitle AS recipe
FROM recipeIng il JOIN 
     Ingredient i ON  il.ingredientID = i.ingredientID JOIN
     Recipe r ON il.recipeID = r.recipeID
WHERE i.ING LIKE '%cheese%' 
AND   i.ING LIKE '%salmon%'

At this point, the issue should be clear - there are 2 likely possibilities, and 2 is more likely than 1.

1) Your ING field stores all the ingredients for a recipe in a single field. If this is the case then you do not have a recipe who's ingredients call for both Cheese and Salmon.

2) Your ING field only stores 1 ingredient per row. However, you are asking for a single row which contains both Cheese and Salmon. This is not your intention, and the query is therefore wrong.

-- SELECT ALL RECIPES USING CHEESE *OR* SALMON
SELECT r.recipeTitle AS recipe
FROM recipeIng il JOIN 
     Ingredient i ON  il.ingredientID = i.ingredientID JOIN
     Recipe r ON il.recipeID = r.recipeID
WHERE i.ING LIKE '%cheese%' 
AND   i.ING LIKE '%salmon%'

-- SELECT ALL RECIPES USING CHEESE *AND* SALMON
SELECT r.recipeTitle AS recipe
FROM recipeIng il JOIN 
     Ingredient iCheese 
         ON  il.ingredientID = i.ingredientID 
         AND i.ING LIKE '%cheese%'  JOIN
     Ingredient iSalmon 
         ON  il.ingredientID = i.ingredientID 
         AND i.ING LIKE '%salmon%'  JOIN 
     Recipe r ON il.recipeID = r.recipeID

Please note the above are for example only - without knowing your schema, these are merely hints and suggestions :)

Collectives™ on Stack Overflow

MySQL 'AND' query not working as expected

3 Answers 3

2 Comments

SQL Fiddle Demo

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related