Use Join Where

Question

I have the following tables.

Table Name: Recipe
id  topic    description    difficultylevel     totaltime
1   Cheese   Cheese         Easy                10
2   Cheese1  Cheese1        Medium              50

Table Name: Ingredients
id  recipeid    ingredient
1   1           Butter
2   1           Cheese
3   1           Bread

Now, when I run the following query, it returns the recipe id = "1", although it should not, because I do not want a recipe in which there is butter.

SELECT recipe.id
FROM 
  `recipe`
  INNER JOIN ingredients ON recipe.id = ingredients.recipeid
WHERE 
  (recipe.description LIKE '%CHEESE%' OR recipe.topic LIKE '%CHEESE%')
  AND (recipe.difficultylevel='Easy')
  AND (recipe.totaltime <= 30)
  AND (ingredients.ingredient <> 'Butter')

It returns recipe.id = "1" twice because of cheese and bread. I need to fix the query so that it doesn't include recipe.id = "1" at all if it has Butter (for example)

Answer 1

I do not think you want inner joinif I understand your question. You can use outer joinwith a tag null, or you can use not exists:

select id
from recipe r
where 
    (recipe.description LIKE '%CHEESE%' OR recipe.topic LIKE '%CHEESE%')
    AND (recipe.difficultylevel='Easy')
    AND (recipe.totaltime <= 30)
    AND not exists (
        select 1
        from ingredients i
        where r.id = i.recipeid
             and i.ingredient = 'Butter'
    )

Answer 2

, , Butter, , LEFT JOIN, = 'Butter'. WHERE IS NULL ingredients, , .

SELECT recipe.id
FROM
  recipe
  -- Join condition specifically looks for 'Butter' (other ingredients may be chained with OR inside () )
  LEFT JOIN ingredients ON recipe.id = ingredients.recipe_id AND (ingredient = 'Butter')
WHERE
  (recipe.description LIKE '%CHEESE%' OR recipe.topic LIKE '%CHEESE%')
  AND (recipe.difficultylevel='Easy')
  AND (recipe.totaltime <= 30)
  -- NULL condition for matching ingredient
  AND (ingredients.ingredient IS NULL)

, 1 : http://sqlfiddle.com/#!2/73f975/1