How to choose all the dishes consisting only of flour, eggs and sugar?

Question

Oracle

There are Dishes and a list of Ingredients for them (link N to N). How to choose all the dishes consisting only of flour, eggs and sugar?

Answer 1

Collect all the ingredients in one line and compare it with the list. LISTAGG .

SELECT dish_name FROM dish
  JOIN dish_ingredients …
  JOIN ingredients …
  GROUP BY dish_name
  HAVING ( LISTAGG( UPPER( ingredient_description ), ',' ) 
           WITHIN GROUP ( ORDER BY ingredient_description )) = 'МУКА,САХАР,ЯЙЦО'

you must have a list of words in alphabetical order

SELECT UPPER( LISTAGG( Name, ',' )) FROM
( SELECT 'мука' AS Name FROM dual
UNION
SELECT 'яйцо' FROM dual
UNION
SELECT 'сахар' FROM dual
ORDER BY 1 )