SQL query recipes with these ingredient(s) but NOT these ingredient(s)

Question

I am doing an advanced search functionality for a recipes website where the users can query;

• Recipes with the presence of certain ingredients (up to 3 ingredients)
• Recipes without the presence of certain ingredients (up to 3 ingredients)
• Under specific cooking time

The above-mentioned points can be combined together in the user query.

I'm already half way through where I can query the recipes that include specified ingredients and cooked under certain amount of time.

Here's my database structure;

Table: Recipes

Recipe_ID, Recipe_Name, Cooking_time

Table: Ingredients

Ingredient_ID, Ingredient_Name

Table: Recipe_Ingredients

Recipe_Ingredient_ID, Ingredient_ID, Recipe_ID

Here's my SQL query so far;

SELECT count(*) as rowcount, r.Recipe_name
FROM Recipes AS r
INNER JOIN Recipe_Ingredients AS ri 
ON r.Recipe_ID = ri.Recipe_ID
INNER JOIN Ingredients AS i
ON ri.Ingredient_ID = i.Ingredient_ID 
AND i.Ingredient_Name IN ('penne','onion')
AND r.Cooking_time < 60
GROUP BY r.Recipe_name HAVING rowcount = 2;

The will get recipes that contain 'penne' and 'onion' and cooked in less than 60 minutes.

What I can't figure out is how to query recipes as per below;

• contain 'penne' and 'onion'
• does not contain 'butter'
• cooked in 'less than 60 minutes'

I have tried the below code but it does not work;

SELECT count(*) as rowcount, r.Recipe_name
FROM Recipes AS r
INNER JOIN Recipe_Ingredients AS ri 
ON r.Recipe_ID = ri.Recipe_ID
INNER JOIN Ingredients AS i
ON ri.Ingredient_ID = i.Ingredient_ID 
AND i.Ingredient_Name IN ('penne','onion')
AND i.Ingredient_Name NOT IN ('butter')
AND r.Cooking_time < 60
GROUP BY r.Recipe_name HAVING rowcount = 2;

Any help is much appreciated!

Thanks.

Answer 1

You can use

SELECT r.Recipe_name,
       r.Recipe_ID
FROM   Recipes AS r
       INNER JOIN Recipe_Ingredients AS ri
               ON r.Recipe_ID = ri.Recipe_ID
       INNER JOIN Ingredients AS i
               ON ri.Ingredient_ID = i.Ingredient_ID
WHERE  i.Ingredient_Name IN ( 'penne', 'onion', 'butter' )
       AND r.Cooking_time < 60
GROUP  BY r.Recipe_ID, /*<-- In case two recipes with same name*/
          r.Recipe_name
HAVING
/*Must contain both these*/
COUNT(DISTINCT CASE
                 WHEN i.Ingredient_Name IN ( 'penne', 'onion' ) THEN i.Ingredient_Name
               END) = 2
AND
/*Can't contain these*/
MAX(CASE
      WHEN i.Ingredient_Name IN ( 'butter' ) THEN 1
      ELSE 0
    END) = 0 

Answer 2

Use NOT EXIST clause with a subquery:

NOT EXISTS(
  SELECT 1 FROM Ingredients i
  WHERE ri.Ingredient_ID = i.Ingredient_ID 
  AND i.Ingredient_Name  IN ('butter')
)

A full query:

SELECT count(*) as rowcount, r.Recipe_name
FROM Recipes AS r
INNER JOIN Recipe_Ingredients AS ri 
ON r.Recipe_ID = ri.Recipe_ID
INNER JOIN Ingredients AS i
ON ri.Ingredient_ID = i.Ingredient_ID 
AND i.Ingredient_Name IN ('penne','onion')
AND     NOT EXISTS(
  SELECT 1 FROM Ingredients i
  WHERE ri.Ingredient_ID = i.Ingredient_ID 
  AND i.Ingredient_Name  IN ('butter')
)
AND r.Cooking_time < 60
GROUP BY r.Recipe_name HAVING rowcount = 2;

Answer 3

You might also use GROUP_CONCAT() with REGEXP (OK, probably not the best solution, but might be OK for more complex queries):

SELECT r.Recipe_Name
  FROM Recipes r INNER JOIN Recipe_Ingredients ri
    ON r.Recipe_ID = ri.Recipe_ID
 INNER JOIN Ingredients i
    ON ri.Ingredient_ID = i.Ingredient_ID
 WHERE r.Cooking_Time <= 60
   AND i.Ingredient_Name IN ('butter','onion','penne')
 GROUP BY r.Recipe_Name
HAVING 'onion' REGEXP CONCAT('^(', GROUP_CONCAT(r.Ingredient_Name SEPARATOR '|'), ')$')
   AND 'penne' REGEXP CONCAT('^(', GROUP_CONCAT(r.Ingredient_Name SEPARATOR '|'), ')$')
   AND 'butter' NOT REGEXP CONCAT('^(', GROUP_CONCAT(r.Ingredient_Name SEPARATOR '|'), ')$');

See SQL Fiddle demo here.