Как выбрать все блюда, состоящие только из муки, яиц и сахара?

Question

[Murad Kasp]

Есть Блюда и список Ингредиентов к ним (связь N к N). Как выбрать все блюда, состоящие только из муки, яиц и сахара?

Comments

[FanatPHP]

ожно ерез жойн, ожно ерез гуп бай и эвинг

[Murad Kasp]

FanatPHP, Делал нечто подобное

select 
  dh.dish_name 
from 
  dish_ingredients ds, 
  ingredients ing, 
  dish dh 
where 
  ds.dish_id in (
    select 
      ds.dish_id 
    from 
      dish_ingredients ds 
    group by 
      ds.dish_id 
    having 
      count(ds.ingredient_id) = 3
  ) 
  and upper(ing.ingredient_description) in ('МУКА', 'САХАР', 'ЯЙЦО') 
  and ds.ingredient_id = ing.id 
  and ds.dish_id = dh.id
 group by dh.dish_name

но мне нужно 3 значения сразу проверялись

[FanatPHP]

какая-то ерунда во where. что должен означать этот подзапрос?
группировка должна быть на весь запрос

но если такие сложности то предлагаю переписать через джойны. Так будет проще

[Murad Kasp]

FanatPHP, Прощу прощения

having 
      count(ds.ingredient_id) = 3

должен быть равен 3 т. к. ингредиентов 3
Логика бала такая, раз ингредиентов 3 то этим подзапросом я отсеиваю те блюда у которых более 3 ингредиентов.
Продемонстрируйте если не сложно.

[FanatPHP]

хм, а действительно ведь надо отсекать.
надо подумать

[FanatPHP]

по сути надо к основному запросу тоже хэвинг добавить. но мне не нравится этот запрос с двумя группировками, он лагать будет как не дай бог

Answer 1

[Константин Цветков]

Собрать все ингредиенты в одну строку и сравнить её со списком. 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 )) = 'МУКА,САХАР,ЯЙЦО'

у вас список слов должен быть обязательно в алфавитном порядке

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

Comments

[Murad Kasp]

А можно по подробнее?

[Константин Цветков]

Murad Kasp, По ссылке есть примеры. По аналогии собираете ингредиенты в одну строку и если она равна 'МУКА,САХАР,ЯЙЦО' получаете ваше блюдо.

[Murad Kasp]

Все, разобрался. Спасибо

select 
  d.dish_name 
from 
  (
    select 
      di.dish_id, 
      LISTAGG(di.ingredient_id, ',') WITHIN GROUP (
        order by 
          di.ingredient_id
      ) lst 
    from 
      dish_ingredients di 
    group by 
      di.dish_id
  ) foo, 
  dish d 
where 
  foo.lst = (
    select 
      LISTAGG(id, ',') WITHIN GROUP (
        ORDER BY 
          id
      ) 
    from 
      ingredients ing 
    where 
      upper(ing.ingredient_description) in ('МУКА', 'САХАР', 'ЯЙЦО')
  ) 
  and d.id = foo.dish_id

[Константин Цветков]

Murad Kasp, Нет. Совершенно по другому.

[Murad Kasp]

Константин Цветков, да выглядит не очень, но работает)
Ваш код изящнее, но у вас список слов должен быть обязательно в алфавитном порядке.