Chocolates and Cream

0

Please login or register to vote for this query.

(click on this box to dismiss)

Chocolates and Cream

Stack Overflow

Q&A for professional and enthusiast programmers

-- Testing
-- Chocolates and Cream

WITH Recipes AS
(
SELECT 1 AS id, 'chocolate cream pie' AS name
UNION ALL
SELECT 2, 'banana cream pie'
UNION ALL
SELECT 3, 'chocolate banana surprise'
), 
Ingredients AS
(
SELECT 1 AS id, 'banana' AS name
UNION ALL
SELECT 2, 'cream'
UNION ALL
SELECT 3, 'chocolate'
),
RecipeIngredients AS
(
SELECT 1 AS recipe_id, 2 AS ingredient_id
UNION ALL
SELECT 1 AS recipe_id, 3 AS ingredient_id
UNION ALL
SELECT 2 AS recipe_id, 1 AS ingredient_id
UNION ALL
SELECT 2 AS recipe_id, 2 AS ingredient_id
UNION ALL
SELECT 3 AS recipe_id, 1 AS ingredient_id
UNION ALL
SELECT 3 AS recipe_id, 3 AS ingredient_id 
)

SELECT DISTINCT r.name
  FROM RECIPES r
  JOIN RECIPEINGREDIENTS ri ON ri.recipe_id = r.id
  JOIN INGREDIENTS choc ON choc.id = ri.ingredient_id
                       AND choc.name = 'chocolate'
  JOIN INGREDIENTS cream ON cream.id = ri.ingredient_id
                        AND cream.name = 'cream'

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results