Множественные объединения c условием?

Question

[Origihor]

SELECT 
  t1.id,
  t1.name AS product,
  t3.name AS color,
  t5.name AS size
FROM
  `product` AS t1
JOIN
  `product_color` AS t2 ON t1.id = t2.product_id
JOIN
  `color` AS t3 ON t2.color_id = t3.id
JOIN 
  `product_size` AS t4 ON t1.id = t4.product_id
JOIN 
  `size` AS t5 ON t4.size_id = t5.id

Вопрос:
Как получить список товаров удовлетворяющий сразу всем перечисленным ниже условиям?

• черный цвет и размер S
  
• черный цвет и размер M
  
• размер L
  

Псевдокод:

WHERE
 (color = "black" and size = "S")
and
 (color = "black" and size = "M")
and
 (size = "L")

Обратите внимание, свойства "цвет" и "размер" - это поля разных таблиц.
В таблице всего два товара и всем перечисленным условиям удовлетворяет "jacket A".

В реальности, есть множество сущностей узкоспециализированной направленности, свойства этих сущностей хранятся с помощью N:M. Некоторые из этих свойств допускают вложенности, в результате чего, обычный select превращается в строку солидной длинны, не говоря уже о запросе с условием. Может я упустил что, ткните пальцем если не сложно.

Answer 1

[Марат]

По моему все можно :-) ибо
Я бы делал через subquery (удобно для выборок по разным условиям из составленного "набора" пересечений)

select BSproduct from
  ( -- Список продуктов имеющих черный цвет и малый размер
    select product as BSProduct from
      (
        SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
          FROM  `product` AS t1
          JOIN `product_color` AS t2 ON t1.id = t2.product_id
          JOIN `color` AS t3 ON t2.color_id = t3.id
          JOIN `product_size` AS t4 ON t1.id = t4.product_id
          JOIN `size` AS t5 ON t4.size_id = t5.id
      ) 
    where color = "black" and size = "S"  
  ) as BlackAndSmallSize, 
  ( -- Список продуктов имеющих черный цвет и средний размер
    select product as BMProduct from
      (
        SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
          FROM  `product` AS t1
          JOIN `product_color` AS t2 ON t1.id = t2.product_id
          JOIN `color` AS t3 ON t2.color_id = t3.id
          JOIN `product_size` AS t4 ON t1.id = t4.product_id
          JOIN `size` AS t5 ON t4.size_id = t5.id
      ) 
    where color = "black" and size = "M"  
  ) as BlackAndMidSize, 
  ( -- Список продуктов имеющих большой размер
    select product as LProduct from
      (
        SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
          FROM  `product` AS t1
          JOIN `product_color` AS t2 ON t1.id = t2.product_id
          JOIN `color` AS t3 ON t2.color_id = t3.id
          JOIN `product_size` AS t4 ON t1.id = t4.product_id
          JOIN `size` AS t5 ON t4.size_id = t5.id
      ) 
    where size = "L"  
  ) as LargeSize
where BSProduct=BMProduct and BMProduct=LProduct

Не знаю как насчет MySQL и его форков, в некоторых SQL серверах можно заранее задавать подзапросы типа

(
        SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
          FROM  `product` AS t1
          JOIN `product_color` AS t2 ON t1.id = t2.product_id
          JOIN `color` AS t3 ON t2.color_id = t3.id
          JOIN `product_size` AS t4 ON t1.id = t4.product_id
          JOIN `size` AS t5 ON t4.size_id = t5.id
      )

с помощью команды WITH - тогда объем текста выборки сократится в разы.

Comments

[Origihor]

Вероятно, ваше решение верное, во всяком случае принцип тот же, что использовал nozzy - поиск множеств удовлетворяющих определенным условиям с последующим объединением. Выполнить ваш запрос я так и не смог - ошибки, а "знаний" sql'а для их исправления не хватило.

[Марат]

Origihor: Оказывается для MySQL необходимо проставлять алиасы для подзапросов (для Oracle это не обязательно):
Проверил на первом попавшемся форке MySQL www.tutorialspoint.com/mysql_terminal_online.php

select BSproduct from
  ( -- Список продуктов имеющих черный цвет и малый размер
    select product as BSProduct from
      (
        SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
          FROM  `product` AS t1
          JOIN `product_color` AS t2 ON t1.id = t2.product_id
          JOIN `color` AS t3 ON t2.color_id = t3.id
          JOIN `product_size` AS t4 ON t1.id = t4.product_id
          JOIN `size` AS t5 ON t4.size_id = t5.id
      ) as nested1
    where color = "black" and size = "S"  
  ) as BlackAndSmallSize, 
  ( -- Список продуктов имеющих черный цвет и средний размер
    select product as BMProduct from
      (
        SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
          FROM  `product` AS t1
          JOIN `product_color` AS t2 ON t1.id = t2.product_id
          JOIN `color` AS t3 ON t2.color_id = t3.id
          JOIN `product_size` AS t4 ON t1.id = t4.product_id
          JOIN `size` AS t5 ON t4.size_id = t5.id
      ) as nested2
    where color = "black" and size = "M"  
  ) as BlackAndMidSize, 
  ( -- Список продуктов имеющих большой размер
    select product as LProduct from
      (
        SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
          FROM  `product` AS t1
          JOIN `product_color` AS t2 ON t1.id = t2.product_id
          JOIN `color` AS t3 ON t2.color_id = t3.id
          JOIN `product_size` AS t4 ON t1.id = t4.product_id
          JOIN `size` AS t5 ON t4.size_id = t5.id
      ) as nested3
    where size = "L"  
  ) as LargeSize
where BSProduct=BMProduct and BMProduct=LProduct

Answer 2

[nozzy]

select 
t1.*,
t3.name 
from 
product t1
inner join (
	select 
	product_id
	from product_size
	inner join size on id = size_id
	where name in ('S', 'M', 'L')
	group by product_id
	having count(product_id) = 3
) t2 on t2.product_id = t1.id
inner join (
	select 
	product_id 
	,name
	from product_color
	inner join color on id = color_id
	where name = 'black'
) t3 on t3.product_id = t2.product_id

Comments

[Origihor]

Спасибо, то что нужно.

[Origihor]

nozzy Хочется посмотреть "реальные" примеры по теме. Может подскажите, как сформулировать вопрос для гугла?

[nozzy]

Наверно как-то так:
sql select multiple values in one column

PS Здесь несколько вариантов решения задачи:
dba.stackexchange.com/questions/45512/how-do-i-sel...

Answer 3

[Rsa97]

У вас цвет и размер - независимые атрибуты товара, поэтому получается, что в каждом из имеющихся цветов товар есть в каждом из имеющихся размеров. Таким образом, точно по вашему условию составить запрос невозможно, из (чёрный S & чёрный M & любой L) оно вырождается в (чёрный & S & M & L).

SELECT `p`.`id` 
    FROM `product` AS `p`
    JOIN `product_color` AS `pc1` ON `pc1`.`product_id` = `p`.`id`
    JOIN `color` AS `c1` ON `c1`.`id` = `pc1`.`color_id` 
        AND `c1`.`name` = 'black'
    JOIN `product_size` AS `ps1` ON `ps1`.`product_id` = `p`.`id`
    JOIN `size` AS `s1` ON `s1`.`id` = `ps1`.`size_is` 
        AND `s1`.`name` = 'S'
    JOIN `product_size` AS `ps2` ON `ps2`.`product_id` = `p`.`id`
    JOIN `size` AS `s2` ON `s2`.`id` = `ps2`.`size_is` 
        AND `s2`.`name` = 'M'
    JOIN `product_size` AS `ps3` ON `ps3`.`product_id` = `p`.`id`
    JOIN `size` AS `s3` ON `s3`.`id` = `ps3`.`size_is` 
        AND `s3`.`name` = 'L'

Comments

[Origihor]

Да, вы правы. Я ошибся когда составлял пример...

Answer 4

[Aleksey Ratnikov]

так и пишите, как вы сказали:

WHERE
 (t3.name = 'black' and t5.name = 'S')
and
 (t3.name = 'black' and t5.name = 'M')
and
 (t5.name = 'L')

WHERE фильтрует уже результаты джоина и там все эти поля будут. Не забывайте, что алиасы использовать в WHERE нельзя - он исполнятся до SELECT, где эти алиасы определяются

Comments

[Origihor]

Вы не правы. По описанному выше условию осуществляется поиск строки (одной строки) в результирующей таблице (последний скриншот) удовлетворяющей всем условиям в секции where, а её не существует.

Результат запроса - ноль строк.

[Станислав Макаров]

Origihor может все-таки надо or между двумя строками условий? А то как-то t5.name = 'S' and t5.name = 'M' никогда ни одну строку не дадут. Aleksey вам пытался донести основную мысль, и на это внимание не обратил.

[Станислав Макаров]

Origihor наконец понял чего вы хотите - чтобы выводились товары, которые есть сразу в трех указанных вами разновидностях. Что ж, тогда могу посоветовать вам оставить or вместо and, сделать count(product_id), group by product_id и having count(product_id) >= 3;

О, давайте засуммоним Rsa97 , может он чего получше подскажет)