Как в sql выбрать строки, которые содержат сразу несколько тегов?

Question

[Barrakuda74]

Ребят как в sql выбрать строки, которые содержат сразу несколько тегов?

Имеем две таблицы:

FruitsTABLE
id   name
1    яблоко
2    банан
3    апельсин

FruitTagTABLE
fruit    tag
1        сладкий
1        круглый
2        сладкий
3        сладкий
3        круглый

У меня получилось сделать запрос фруктов, которые содержат какой-либо 1 тег:

SELECT DISTINCT fruitsTABLE.* FROM FruitsTABLE JOIN FruitTagTABLE ON FruitsTABLE.id = FruitTagTABLE.id WHERE FruitTagTABLE.tag = 'сладкий' OR FruitTagTABLE.tag = 'круглый'

Т.е. объединяем две таблицы через JOIN, находим в каких фруктах есть 'сладкий' или 'круглый' и избавляемся от дублей через DISTINCT.

А как мне получить только фрукты, которые одновременно И сладкие И круглые?
На самом деле у меня 3 таблицы, поэтому вместо "круглый" "сладкий" там только ID тегов указаны, т.е. 1 и 2.
Предполагаю такой вариант, объединение через GROUP_CONCAT для получения ячейки "1,2", а потом запрос какой-то чтобы искать где содержится у фруктов в такой ячейке и "1", и "2", только где гарантия, что он не найдёт мне фрукты с тегами "1,22".

В общем думается мне что как-то попроще можно решить этот вопрос. Не подскажете в каком направлении копать?

Answer 1

[Rsa97]

SELECT `f`.*
  FROM `FruitsTABLE` AS `f`
  JOIN `FruitTagTABLE` AS `ft1` ON `ft1`.`tag` = :tag1 AND `ft1`.`id` = `f`.`id`
  JOIN `FruitTagTABLE` AS `ft2` ON `ft2`.`tag` = :tag2 AND `ft1`.`id` = `f`.`id`

Comments

[Barrakuda74]

Спасибо огромное! Блин, это работает! И разобрав запрос, даже понимаю, как это всё работает! Но, чёрт возьми, я не понимаю, как я должен был до этого додуматься? :((((((((

[Rsa97]

Barrakuda74: А что тут думать - начала теории множеств. Пересечение множества элементов, имеющих тэг tag1, с множеством элементов, имеющих тэг tag2.

[Вадим Гук]

Это мне тоже очень помогло.
Искал решение уже второй день.
Пока не знаю, как оно так работает, но разберусь)))

Answer 2

[Eduard]

select * 
from FruitsTABLE f
where name = 'яблоко'
  and exists (select * 
              from FruitTagTABLE t 
              where t.fruit = f.fruit
                and f.tag  = 'сладкий'
             )
  and exists (select * 
              from FruitTagTABLE t 
              where t.fruit = f.fruit
                and f.tag  = 'круглый'
             )

что-то похожее уже было:
https://toster.ru/answer?answer_id=867572

Comments

[Barrakuda74]

Ваш код похоже тоже должен работать, но метод вроде какой-то другой. И, если я не ошибаюсь, здесь подразумевается, что таблицы уже объединены? Если да, то как бы он выглядел, если изначально таблицы разъединены (если у вас есть время конечно показать пример)? Ах да, здесь ещё кажется подразумевается, что ищем только 'яблоко'?

[Eduard]

Barrakuda74: что значит "уже объединены"? Этот метод работает так же как и запрос выше от Rsa97 только при этом у сервера больше возможностей для внутренней оптмизации, так как ищутся не все записи по условию, а только одна. Да, условие по яблоку можно выкинуть, я просто невнимательно прочитал вопрос.

[Rsa97]

Eduard: В худшем случае ваш запрос будет генерировать ещё два запроса на каждую строку исходной таблицы.

[Eduard]

Rsa97: что значит ещё два запроса? там и есть два запроса. Если первый пустой, то второго наверное не будет. С inner join так же, те же два запроса.

[Rsa97]

Eduard: При исполнении вашего запроса для каждой найденной строки с name = 'яблоко' MySQL будет выполнять два дополнительных вложенных SELECT'а.

[Eduard]

Rsa97: так же как и при inner join

[Rsa97]

Eduard: EXPLAIN вашего запроса показывает DEPENDENT SUBQUERY, что заведомо хуже JOIN'а.

[Eduard]

Rsa97: не являясь экспертом в mysql, я, честно говоря, удивлен, что он выполняет такие запросы хуже, чем через inner join. Бегло просмотрев докуметацию, я вижу, что сильно влияет возможность null значений. Может быть в этом дело, но не важно. Я много раз видел, как запросы с "плохими" планами выполнялись быстрее, чем с "хорошими". Но мой опыт больше относится к mssql чем к mysql. В любом случае спасибо за информацию.