Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How many recipes can you make with only milk, eggs, butter, flour, sugar, and salt?

Tags:

sql

postgresql

I have a SQL query that has me stumped. Basically, I have a Recipes table that contains (as you've no doubt guessed,) many recipes. I have an Ingredients table which contains all sorts of ingredients. I have a RecipeIngredients table that links a recipe to what ingredients it uses. Lastly, I have a PopularIngredients table (it's actually a view, but who cares?) that contains the most popular ingredients people might have in their kitchen:

CREATE Table Recipes
(
  RecipeId int4,
  Title varchar(100)
);

CREATE Table Ingredients
(
  IngredientId int4,
  Name varchar(100)
);

CREATE Table RecipeIngredients
(
  RecipeId int4,
  IngredientId int4,
  Amount int2
);

CREATE Table PopularIngredients
(
  IngredientId int4
);

My goal is to get a list of all recipes that use only popular ingredients.

A SQL Fiddle with sample data can be found here.

What I'm looking for is a query that will return Chicken Salad and Pancakes. Aligator Burgers would not be returned, since it uses aligator which is not a popular ingredient.

I've tried a few things involving sub-selects and the ALL keyword, but haven't had any luck. I've tried various inner and outer joins, but Recipe rows will still show up as long as at least one of its ingredients is popular. Any help would be much appreciated!

I'm using Postgres 9.1.

like image 588
Mike Christensen Avatar asked Aug 16 '12 00:08

Mike Christensen


People also ask

What happens when you mix eggs and butter?

The fact is, like oil and vinegar, eggs and butter don't mix naturally. It's a matter of chemistry: Butter is at least 80 percent fat, while eggs contain large amounts of water. So any time you add more than a single egg to creamed butter, it's best to do it slowly to give the mixture time to thicken and emulsify.

What happens when you mix sugar and eggs?

Sugar doesn't actually “cook” eggs the same way heat does but rather changes the chemical structure of the protein in the eggs. When eggs (especially yolks) and sugar sit too long without being mixed, the water in the yolks is absorbed by the sugar and you end up with a hard, clumpy mess.

What does flour and egg do?

In addition to their nutritional value, eggs can provide structure, leavening, richness, color, and flavor to baked products. The height and texture of baked goods is determined by the balance between eggs and flour which provide strength, and sugar and fat which add tenderness.


2 Answers

This gets all recipes which have no ingredients that are not in the PopularIngredients table.

select * from Recipes r where not exists (
  select * from RecipeIngredients ri 
  left join PopularIngredients pi on pi.IngredientId=ri.IngredientId
  where ri.RecipeId=r.RecipeId and pi.IngredientId is null
)
like image 174
Blorgbeard Avatar answered Sep 20 '22 06:09

Blorgbeard


Used WHERE NOT EXISTS to ensure none of the ingredients used are missing from the PopularIngredients view:

SELECT R.*
FROM Recipes R
WHERE NOT EXISTS (
    SELECT 1
    FROM RecipeIngredients RI
    LEFT JOIN PopularIngredients P ON P.IngredientId = RI.IngredientId
    WHERE RI.RecipeId = R.RecipeId AND P.IngredientId IS NULL
)

Updated your SqlFiddle

like image 44
Bort Avatar answered Sep 19 '22 06:09

Bort