Using Join with Where

Question

I have 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" even though it shouldn't because I don't want a recipe which has butter in it.

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" two times because of Cheese and Bread. I need to fix the query so that it excludes the recipe.id = "1" at all if it has Butter (for example)

Answer 1

I don't think you want the inner join if I'm understanding your question. You could use an outer join with a null check, or you could 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

To ensure that recipes including Butter are not in the output, you may use a LEFT JOIN including an = 'Butter' condition. In the main query's WHERE clause, you will want to look for IS NULL on a column for ingredients to ensure you get back non matches.

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)

And here's a demonstration, returning 1 recipe to match your criteria: http://sqlfiddle.com/#!2/73f975/1