Find only complete match in join table-CodePudding

I have 3 tables, Pizza, Ingredient, and PizzaIngredients which is a join table between my first two tables.

Pizza

id	name
1	Margarita
2	Pepperoni
3	Vegetarian

Ingredient

id	name
1	Tomato
2	Pepperoni
3	Lettuce

PizzaIngredients

pizzaId	ingredientId
1	1
2	1
2	2
3	1
3	3

I have a list of ingredients, and I want to list all the pizza I can make with the ingredients I have. To be able to make a pizza, i need to have all its ingredients

If in my list of ingredients I have : [1], I want to return the pizza with id 1 since I can only make the Margarita pizza.
If in my list of ingredients I have : [1, 3], I want to return the pizza with id 1 and 3 since I can potentially make the Margarita or the Vegetarian.

What I've tried :

SELECT
  pi.pizzaId
FROM
  pizzaIngredients pi
WHERE
  pi.ingredientsId NOT IN (
    SELECT
      DISTINCT pi2.ingredientsId
    FROM
      pizzaIngredients pi2
    WHERE
      pi2.ingredientsId NOT IN ('1')
  )

But It also returns me pizza 2 and 3 which I dont want since I don't have all the ingredients. I use a postgresql database

CodePudding user response：

try this :

SELECT pi.pizzaId
  FROM pizzaIngredients pi
 GROUP BY pi.pizzaId
HAVING array_agg(ingredientId) <@ array [1,3]

dbfiddle