find all recipe ingredients in the table

user_14267886 0
Viewed 29

I have two tables. In the first table, all the recipes, in the second, the ingredients of the recipes

Recipes

RecipeId Name
789 Pizza
790 Soup
791 Sushi
792 Cake
793 Sandwich
794 Bread
795 Sausage

Ingredients

id RecipeId Name
1 789 Flour
2 789 Water
795 789 Sausage
3 789 Olives
4 789 Salt
794 793 Bread
795 793 Sausage
1 794 Flour
2 794 Water
4 794 Salt
5 795 Ground meat
4 795 Salt

How can I find the recipe with the most ingredients, including all ingredients, such as a sandwich, which has bread (flour, water, salt) and sausages (ground meat and salt) = 5 ingredients? using sql query

sql
1 Answers

We can use a RANK() based approach here:

WITH cte AS (
    SELECT RecipeId, RANK() OVER (ORDER BY COUNT(*) DESC) rnk
    FROM Ingredients
    GROUP BY RecipeId
)

SELECT r.*
FROM Recipes r
WHERE RecipeId IN (SELECT RecipeId FROM cte WHERE rnk = 1);
user_18632290
Related