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.
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.
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.
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.
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
)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With