SQL Associative Entity question

Question

Hello,

I have a SQL question. I'm working with the following set of tables:

http://imgur.com/eg19r.png

The Recipe table contains 5 recipes, Ingredient contains many ingredients (52), and IngredientList is an associative entity, an implementation of the many-to-many relationship between Recipe and Ingredient. RecipeID and IngredientID are essentially auto-increment ID numbers.

I'm trying to write a query that displays all vegetarian recipes (that is, recipes without

'%beef%',
'%pork%',
'%chicken%',
'%lamb%

in any of their associated Ingredient.Name). The issue I'm running into is that the result set contains one row per ingredient, rather than one row per recipe.

The query that generates this result set is (note that the Ingredient table is named ingredients here):

SELECT recipe.name AS Dish, ingredients.name
 FROM (recipe JOIN ingredientlist ON recipe.recipeid=ingredientlist.recipeid
  JOIN ingredients ON ingredientlist.ingredientid=ingredients.ingredientid)
 WHERE ingredients.name NOT LIKE '%beef%'
  AND ingredients.name NOT LIKE '%chicken%' 
  AND ingredients.name NOT LIKE '%pork%' 
  AND ingredients.name NOT LIKE '%lamb%';

That query generates 50 results (not 52, because two ingredients are excluded based on their ingredient.name containing the a substring excluded by my WHERE clause).

My goal is to return 3 of the 5 recipe names in the recipe table (subtracting those containing meat in the associated ingredients.name).

Answer 1

You might want to add a is_vegetarian flag to your ingredient table.

But you main problem is that you are asking the database to return 50 rows by including ingredients.name in your select clause. If you want only the recipes, you need to ask for only the recipes:

select r.name as dish
from recipe r
where not exists (
  select 1 from ingredients i
  join ingredientlist il on i.ingredientsid=il.ingredientid
  where il.recipeid=r.recipeid
  and i.is_vegetarian = false
)

Answer 2

wallenborn, thanks for your help.

I didn't have the ability to modify the tables at all (this is a question off a school assignment). I wound up emailing my instructor, and he hinted at using a subquery. I wound up with this query:

SELECT r.name AS Dish
    FROM recipe r
    WHERE recipeid NOT IN (
        SELECT il.recipeID
            FROM ingredientlist il, ingredients i
            WHERE (il.ingredientid = i.ingredientid)
            AND (i.name LIKE '%beef%' OR i.name LIKE '%chicken%' OR i.name LIKE '%pork%' OR i.name LIKE '%lamb%'));