By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table product
(
id int auto_increment primary key,
title text null,
stock int default 0 not null,
price float(9,2) default 0.00 not null
);
create table property
(
id int auto_increment primary key,
code varchar(20) null,
title varchar(50) null
);
create table product_property_value
(
id int auto_increment primary key,
product_id int not null,
property_id int not null,
value varchar(255) null,
foreign key (product_id) references product(id),
foreign key (property_id) references property(id)
);
insert into product
(title, stock, price) values
('beans', 42, 10.0)
, ('swagger', 999, 94.0);
insert into property
(code, title) values
('color', 'Color')
, ('width', 'Width')
, ('height', 'Height');
insert into product_property_value
(product_id, property_id, value) values
(2, 1, 'Green')
, (2, 2, 4)
, (2, 3, 4)
, (1, 1, 'Red')
, (1, 2, 4)
, (1, 3, 5);
--
-- products with color = "Red", width=4 or width=5 and height = 5.
--
SELECT title, stock, price, property_values
FROM product prod
, LATERAL (
SELECT ppv.product_id
, MAX(CASE WHEN prop.code = 'color' THEN ppv.value END) AS color
, MAX(CASE WHEN prop.code = 'width' THEN ppv.value END) AS width
, MAX(CASE WHEN prop.code = 'height' THEN ppv.value END) AS height
, GROUP_CONCAT(prop.title, ': ', ppv.value SEPARATOR ', ') AS property_values
FROM product_property_value ppv
LEFT JOIN property prop ON prop.id = ppv.property_id
WHERE ppv.product_id = prod.id
GROUP BY ppv.product_id
HAVING color = 'Red'
AND width IN (4, 5)
AND height = 5
) propies
ORDER BY title;
| title | stock | price | property_values |
|---|---|---|---|
| beans | 42 | 10.00 | Color: Red, Width: 4, Height: 5 |
--
-- products with color = "Red", width=4 or width=5 and height = 5.
--
SELECT prod.title, stock, price
, GROUP_CONCAT(prop.title, ': ', ppv.value SEPARATOR ', ') AS property_values
FROM product prod
JOIN product_property_value ppv
ON ppv.product_id = prod.id
JOIN property prop
ON prop.id = ppv.property_id
AND prop.code IN ('color', 'width', 'height')
GROUP BY prod.id, prod.title, stock, price
HAVING COUNT(CASE
WHEN prop.code = 'color' AND ppv.value = 'Red'
THEN ppv.id END) > 0
AND COUNT(CASE
WHEN prop.code = 'width' AND ppv.value IN (4, 5)
THEN ppv.id END) > 0
AND COUNT(CASE
WHEN prop.code = 'height' AND ppv.value = 5
THEN ppv.id END) > 0
ORDER BY prod.title;
| title | stock | price | property_values |
|---|---|---|---|
| beans | 42 | 10.00 | Color: Red, Width: 4, Height: 5 |