By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tags (`id` int, `tag` varchar(8), `value` varchar(6));
INSERT INTO tags (`id`, `tag`, `value`) VALUES
(1, 'color', 'green'),
(2, 'color', 'yellow'),
(3, 'color', 'red'),
(4, 'pet', 'dog'),
(5, 'pet', 'cat');
CREATE TABLE product_tags_link (`product_id` int, `tag_id` int);
INSERT INTO product_tags_link (`product_id`, `tag_id`) VALUES
(1, 1),
(2, 1),
(2, 2),
(3, 1),
(3, 2),
(3, 4);
CREATE TABLE products (`id` int, `name` varchar(50));
INSERT INTO products (`id`, `name`) VALUES
(1, "test"),
(2, "test2"),
(3, "test3"),
(4, "test4"),
(5, "test5"),
(6, "test6");
SELECT p.id, p.name
FROM products p
INNER JOIN product_tags_link pt ON pt.product_id = p.id
INNER JOIN tags t ON pt.tag_id = t.id
GROUP BY p.id, p.name
HAVING SUM(t.tag = 'color' AND t.value IN ('green', 'red')) > 0
AND SUM(t.tag = 'pet' AND t.value IN ('dog')) > 0
| id | name |
|---|---|
| 3 | test3 |