Select specific values from sql tables

Question

need some help with sql stuff. Look down below for my sql code snippet.

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 product_property_value
(
    id int auto_increment primary key,
    product_id int not null,
    property_id int not null,
    value varchar(255) null
);

create table property
(
    id int auto_increment primary key,
    code varchar(20) null,
    title varchar(50) null
);

I have a statement for select all rows from these three tables with pretty output.

SELECT
    title,
    stock,
    price,
    (
        SELECT GROUP_CONCAT(p.title, ': ', pv.value SEPARATOR ', ')
        FROM product_property_value pv
        INNER JOIN property p on pv.property_id = p.id WHERE pv.product_id = product.id
    ) property_values
FROM product;

My property table is filled with this data:

insert into property (id, code, title) values (1, 'color', 'Color');
insert into property (id, code, title) values (2, 'width', 'Width');
insert into property (id, code, title) values (3, 'height', 'Height');

My product_property_value table is filled with this data (there is one example for one product, table have many of it):

insert into product_property_value (id, product_id, property_id, value) values (4, 2, 1, 'Green');
insert into product_property_value (id, product_id, property_id, value) values (5, 2, 2, 4);
insert into product_property_value (id, product_id, property_id, value) values (6, 2, 3, 4);

So the main question is how to select products only with specified values: Select products with color = "Red", width=4 or width=5 and height = 5. I need to modify statement or change it but anyway i need to save prettified output. Feel free to answer, thanks.

You have 3 separate RDBMS's listed in your tags. Please update your tags to reflect the actual RDBMS you are using as the answer may be specific to that platform. — Chad Baldwin
– Chad Baldwin, Commented Nov 30, 2021 at 18:15

LukStorms · Accepted Answer · 2021-12-01 10:44:27Z

2

If your version of MySql supports Lateral Derived Tables, then you can try using that.

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;

Or just join them all.

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

Demo on db<>fiddle here

edited Dec 1, 2021 at 10:44

answered Nov 30, 2021 at 20:03

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lilly Over a year ago

works as expected! thanks

LukStorms Over a year ago

Thanks. Btw, will you use the lateral or the 2nd? A warning, if you would change the GROUP BY of the 2nd, stuff like SUM(stock) would give wrong results, because of the many-to-many relation to the properties. You would have to wrap it in a sub-query.

Lilly Over a year ago

i would like to use the second option, cause it's just more clear to me

Mana S · Accepted Answer · 2021-11-30 18:26:01Z

0

I think there needs to be a where clause to your sql statement. One way to do this is:

SELECT
    title,
    stock,
    price,
    (
        SELECT GROUP_CONCAT(p.title, ': ', pv.value SEPARATOR ', ')
        FROM product_property_value pv
        INNER JOIN property p on pv.property_id = p.id WHERE pv.product_id = product.id
    ) property_values
FROM product WHERE property_values='Color: Red, Width: 4, Height: 5' OR property_values='Color: Red, Width: 5, Height: 5';

answered Nov 30, 2021 at 18:26

Mana S

5193 silver badges6 bronze badges

Collectives™ on Stack Overflow

Select specific values from sql tables

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related