Geek Answers Handbook

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

I have a SQL query that puzzles me. Basically, I have a Recipes table containing (as you no doubt guessed) many recipes. I have an Ingredients table containing all kinds of ingredients. I have a RecipeIngredients table that associates a recipe with the ingredients that it uses. Finally, I have a table PopularIngredients (actually this view, but who cares?), Which contains the most popular ingredients that people can 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.

The SQL script with sample data can be found here .

What I'm looking for is a request that will return chicken salad and pancakes. Aligator Burgers will not be returned, as it uses aligator, which is not a popular ingredient.

I tried a few things related to subsamples and the keyword ALL , but they were out of luck. I have tried various internal and external connections, but the recipe lines will be displayed until at least one of its ingredients is popular. Any help would be greatly appreciated!

I am using Postgres 9.1.

+8
sql postgresql
source share
3 answers

This gets all recipes that 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 )
+7
source share

Used by WHERE NOT EXISTS to ensure that none of the ingredients are used in 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 SqlFiddle

+5
source share
 select r.Title from Recipes r join RecipeIngredients ri on r.RecipeId = ri.RecipeId left outer join PopularIngredients pi on ri.IngredientId = pi.IngredientId group by r.Title having count( case when pi.IngredientId is null then 1 end )=0

or almost the same

 select r.Title from Recipes r join RecipeIngredients ri on r.RecipeId = ri.RecipeId left outer join PopularIngredients pi on ri.IngredientId = pi.IngredientId group by r.Title having count(pi.IngredientId)=count(ri.IngredientId)
+2
source share

More articles:


All Articles