Принадлежность к определенному множеству — и не к какому другому [sql]

Question

[Max B]

Привет хабр.

Я слегка вскипел себе голову, возможно простой задачей, но на ум ничего не идет.

Есть у нас Множества X, например (1,2; 2,3,4; 7,9,1; 1,0), есть множество Y, например (1,2,3,4,5,6,7,8)
Можно ли показать из X только то, что в полной мере присутствует в Y? В примере подходит только (1,2; 2,3,4).

Если по-простому то же что делает IN конструкция, но не для каждого члена (1 есть в Y, значит удовлетворяет условию), а по всей коллекции.
Псевдосинтаксис:

SELECT t1.id FROM table1 t1, table2 t2 WHERE t2.key NOT FULLY IN(SELECT id FROM table3)

Answer 1

[ComodoHacker]

Начнем с понимания того, что X это множество множеств, а Y это множество элементов. Поэтому, сравнивать можно (и нужно) Y и элементы X, либо элементы Y и «элементы элементов» X.

Дальше, обозначим элемент X как x. То, что x является подмножеством Y, на SQL можно выразить так

not exists (
    select 1
      from x left join Y on x.id = Y.id
     where Y.id is null
)

Дальше, возвращаясь к вашей задаче, нужно уточнить, как у вас хранится множество X. Например, если в одной таблице X (set_id, element_id), то можно соединить ее с Y внешним соединением, как выше, а затем отфильтровать множества, не полностью содержащиеся в Y. Для этого хорошо подойдут аналитические функции, если ваша СУБД их поддерживает в должном объеме. Опять требуется уточнение.

Comments

[Max B]

В уточнии ответа от Silver_Clash написал подробнее.
Спасибо за ответ — и правда каша в голове, даже вопрос сформулировать нормально не могу.

[ComodoHacker]

OK, у нас MySQL, аналитических функций нет, множества пусть заданы как
X (x,y) и Y (id). Можно сделать так:

select X.x
  from X left join Y on X.y = Y.id
 group by X.x
having count(*) = count(Y.id)

Пояснение. Присоединяем Y внешним соединением. в результате получаем для каждого y либо соответствие Y.id либо null, сколько бы раз этот y ни встретился. Дальше группируем по x, строки с элементами x сворачиваются до одной строки. В результате каждая строка соответствует элементу множества X в начальной постановке). Теперь отфильтруем неподходяшие x; критерием служит то, что общее количество элементов в группе, count(*), равно количеству входящих в Y, count(Y.id) считает только не null значения.

[Max B]

Спасибо за толковую мысль.

Answer 2

[Silver_Clash]

Может что то такое подойдет:

select t1.id from table t1 where t1.key NOT in (подзапрос транспонирующий столбец в строку при помощи SYS_CONNECT_BY_PATH...) ???

то что в скобочках сразу не напишу, надо вспомнить :)

Comments

[Silver_Clash]

Как то сумбурно у вас все написано.
Решайте поэтапно — сперва напишите запрос для определения, включается ли одно множество в другое. Затем напишите запрос выбирающий все множества, для которых первый вопрос возвращает 1.

[Max B]

По-проще наверное будет так.
В результате GROUP_CONCAT() я получаю в консоле mysql
x | y
1 | 1,2
2 | 2,7
3 | 6,8
4 | 2,1

Развернуто без группировки:
x | y
1 | 1
1 | 2
2 | 2
2 | 7
3 | 6
3 | 8
4 | 2
4 | 1

Так вот, у меня есть список(получаемый другим запросом), которому обязаны удовлетворять члены из множества Y для текущего X. Но я не знаю как проверить на принадлежность их всех без GROUP_CONCAT и хреновой тучи строковых функций. NOT in работает так, что если у нас есть попадание хотя бы в одном y, x уже будет в выборке, а это не то.