How to get records from a related table if you need to find records whose columns have different values?

Question

SQL

There is a table `goods`associated with it `goods_property`.
Scheme:


I need to get products that have both properties :
1) `goods_property`.`type`= caffeine_capacity + `goods_property`.`value` = small
2) `goods_property`.`type` = color_type+ `goods_property`.`value` = green.
In layman's terms: Get products where the coffee capacity parameters are "small" and the color is "green".

Structure&Demo data

cidr.sql

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


/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@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 [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;

(I would be grateful for advice from Online Sandbox SQL for such questions)

My attempts
Tried to build several queries, subqueries, but every time I got an unsatisfactory result.
An example for clarity with data from the demo:

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'))

The entry 'Tea "Salmon Spawning"' is in the sample, but it has the wrong "caffeine_capacity".

Answer 1

Option 1. Works if each combination (`goods_id`, `type`, `value`) occurs at most once in `goods_property`.

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`

Option 2. If the table is a curve and the combination can occur more than once, then

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'

Option 3. Also for a curved table.

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'
  )

As a sandbox try https://www.db-fiddle.com/