Select specific values from sql tables-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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