Как получить записи из связанной таблицы, если необходимо найти записи, в столбцах которых разные значения?

Question

[Mark]

Есть таблица `goods`, связанная с ней `goods_property`.
Схема:


Мне необходимо получить товары, у которых есть оба свойства:
1) `goods_property`.`type`= caffeine_capacity + `goods_property`.`value` = small
2) `goods_property`.`type` = color_type + `goods_property`.`value` = green.
Человеческим языком: Получить товары, где параметры вместительности кофе "малый", и цвет "зеленый".

Структура&Демо данные

cidr.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- База данных: `cidr`
--

-- --------------------------------------------------------

--
-- Структура таблицы `goods`
--

CREATE TABLE `goods` (
  `id` int(11) NOT NULL,
  `name` varchar(246) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Дамп данных таблицы `goods`
--

INSERT INTO `goods` (`id`, `name`) VALUES
(1, 'Чай \"Крепекий\" (Target)'),
(2, 'Чай \"Листья мульчачув\"'),
(3, 'Чай \"Сила горы\" (Target)'),
(4, 'Чай \"Лососий Нерест\" ');

-- --------------------------------------------------------

--
-- Структура таблицы `goods_property`
--

CREATE TABLE `goods_property` (
  `id` int(11) NOT NULL,
  `goods_id` int(11) NOT NULL,
  `type` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Дамп данных таблицы `goods_property`
--

INSERT INTO `goods_property` (`id`, `goods_id`, `type`, `value`) VALUES
(7, 1, 'caffeine_capacity', 'small'),
(8, 1, 'leaf_type', 'whole'),
(9, 1, 'color_type', 'green'),
(10, 2, 'caffeine_capacity', 'average'),
(11, 2, 'color_type', 'red'),
(12, 3, 'caffeine_capacity', 'small'),
(13, 3, 'leaf_type', 'crushed'),
(14, 3, 'color_type', 'green'),
(15, 4, 'color_type', 'red'),
(16, 4, 'caffeine_capacity', 'small');

--
-- Индексы сохранённых таблиц
--

--
-- Индексы таблицы `goods`
--
ALTER TABLE `goods`
  ADD PRIMARY KEY (`id`);

--
-- Индексы таблицы `goods_property`
--
ALTER TABLE `goods_property`
  ADD PRIMARY KEY (`id`),
  ADD KEY `goods_id` (`goods_id`);

--
-- AUTO_INCREMENT для сохранённых таблиц
--

--
-- AUTO_INCREMENT для таблицы `goods`
--
ALTER TABLE `goods`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT для таблицы `goods_property`
--
ALTER TABLE `goods_property`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- Ограничения внешнего ключа сохраненных таблиц
--

--
-- Ограничения внешнего ключа таблицы `goods_property`
--
ALTER TABLE `goods_property`
  ADD CONSTRAINT `goods_property_ibfk_1` FOREIGN KEY (`goods_id`) REFERENCES `goods` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

(Буду благодарен за рекомендации Online Sandbox SQL для таких вопросов)

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

SELECT * FROM `goods` 
LEFT JOIN `goods_property` ON `goods`.`id` = `goods_property`.`goods_id` 
WHERE ((`goods_property`.`value`='small') AND (`goods_property`.`type`='caffeine_capacity')) 
OR ((`goods_property`.`value`='green') AND (`goods_property`.`type`='color_type'))

Запись 'Чай "Лососий Нерест"' попадает в выборку, но у него неподходящий "caffeine_capacity".

Answer 1

[Rsa97]

Вариант 1. Работает, если в `goods_property` каждая комбинация (`goods_id`, `type`, `value`) встречается не более одного раза.

SELECT `g`.*
  FROM (
    SELECT `goods_id`
      FROM `goods_property`
      WHERE (`type` = 'caffeine_capacity' AND `value` = 'small')
        OR (`type` = 'color_type' AND `value` = 'green')
      GROUP BY `goods_id`
      HAVING COUNT(*) = 2
  ) AS `p`
  JOIN `goods` AS `g` ON `g`.`id` = `p`.`goods_id`

Вариант 2. Если таблица кривая и комбинация может встретиться более одного раза, то

SELECT `g`.*
  FROM `goods` AS `g`
  JOIN `goods_property` AS `p1` ON `p1`.`goods_id` = `g`.`id`
    AND `p1`.`type` = 'caffeine_capacity' AND `p1`.`value` = 'small'
  JOIN `goods_property` AS `p2` ON `p2`.`goods_id` = `g`.`id`
    AND `p2`.`type` = 'color_type'  AND `p2`.`value` = 'green'

Вариант 3. Тоже для кривой таблицы.

SELECT `g`.*
  FROM `goods`
  WHERE `id` IN (
    SELECT `goods_id`
      FROM `goods_property`
      WHERE `type` = 'caffeine_capacity' AND `value` = 'small'
  ) AND `id` IN (
    SELECT `goods_id`
      FROM `goods_property`
      WHERE `type` = 'color_type' AND `value` = 'green'
  )

В качестве песочницы попробуйте https://www.db-fiddle.com/

Comments

[Akina]

Вариант 1 - достаточно HAVING COUNT(DISTINCT type) = 2, и дубли не страшны.
Вариант 2 - нужен SELECT DISTINCT g.*.
Вариант 3 при правильном индексировании самый эффективный.

[Mark]

Akina,

Вариант 3 при правильном индексировании самый эффективный.

Какое правильное индексирование в данном случае?`type`, `value` индексами сделать?

[Akina]

Mark, в зависимости от объёма выборки и статистики данных либо

CREATE INDEX idx ON goods_property (type, value, goods_id);

либо

CREATE INDEX idx ON goods_property (goods_id, type, value);

Скорее всего, второй окажется эффективнее.