Combine rows with MySQL query

Question

I would like to have a query to retrieve the Size and Color that are assigned to Combinations. So far with the following MySQL query, I could manage to retrieve the data in separated rows but could not combine them in a single row.

SELECT pa.`id_product_attribute`, al.`name`
FROM `psfr_product_attribute` pa
JOIN `psfr_product_attribute_combination` pac ON(pa.`id_product_attribute` = pac.`id_product_attribute`)
JOIN `psfr_attribute_lang` al ON(pac.`id_attribute` = al.`id_attribute`)
WHERE `id_product` = 59 AND al.`id_lang` = 1 AND pa.`quantity` > 0
ORDER BY pa.`id_product_attribute`

Result:

195        M 
195        RED
197        L
197        RED
199        XL
199        RED
200        XL
200        BLACK
201        S
201        RED
202        S
202        BLACK

How can I change the querie to get a result like this?

195        M         RED
197        L         RED
199        XL        RED
200        XL        BLACK
201        S         RED
202        S         BLACK

Any help would be greatly appreciated.

Tables:

CREATE TABLE `psfr_product_attribute` (
  `id_product_attribute` int UNSIGNED NOT NULL,
  `id_product` int UNSIGNED NOT NULL,
  `quantity` int NOT NULL DEFAULT '0',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `psfr_product_attribute` (`id_product_attribute`, `id_product`, `quantity`) VALUES
(195, 59, 5),
(196, 59, 0),
(197, 59, 5),
(198, 59, 0),
(199, 59, 5),
(200, 59, 5),
(201, 59, 5),
(202, 59, 5);


CREATE TABLE `psfr_product_attribute_combination` (
  `id_attribute` int UNSIGNED NOT NULL,
  `id_product_attribute` int UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `psfr_product_attribute_combination` (`id_attribute`, `id_product_attribute`) VALUES
(2, 195),
(10, 195),
(2, 196),
(11, 196),
(3, 197),
(10, 197),
(3, 198),
(11, 198),
(4, 199),
(10, 199),
(4, 200),
(11, 200),
(1, 201),
(10, 201),
(1, 202),
(11, 202);


CREATE TABLE `psfr_attribute_lang` (
  `id_attribute` int NOT NULL,
  `id_lang` int NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `psfr_attribute_lang` (`id_attribute`, `id_lang`, `name`) VALUES
(1, 1, 'S'),
(1, 8, 'S'),
(2, 1, 'M'),
(2, 8, 'M'),
(3, 1, 'L'),
(3, 8, 'L'),
(4, 1, 'XL'),
(4, 8, 'XL'),
(10, 1, 'Red'),
(10, 8, 'Vermelho'), //Red
(11, 1, 'Black'),
(11, 8, 'Preto');  //Black

I didn't use the following tables in my query but also leave them because I don´t know if they can be useful:

CREATE TABLE `psfr_attribute` (
  `id_attribute` int NOT NULL,
  `id_attribute_group` int NOT NULL,
  `color` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `position` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `psfr_attribute` (`id_attribute`, `id_attribute_group`, `color`, `position`) VALUES
(1, 1, '', 0),
(2, 1, '', 1),
(3, 1, '', 2),
(4, 1, '', 3),
(10, 2, '#E84C3D', 5),
(11, 2, '#434A54', 6);


CREATE TABLE `psfr_attribute_group` (
  `id_attribute_group` int NOT NULL,
  `is_color_group` tinyint(1) NOT NULL,
  `group_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `position` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `psfr_attribute_group` (`id_attribute_group`, `is_color_group`, `group_type`, `position`) VALUES
(1, 0, 'radio', 1),
(2, 1, 'color', 0),
(4, 0, 'select', 2);

Added an example in my reply of how to do it after you've fetched the long list. — Ph1reman
– Ph1reman, Commented Mar 22, 2022 at 11:38
@RinkeshP Thanks, but I need to have Size and Color in separate columns — n.h.
– n.h., Commented Mar 22, 2022 at 11:45
Heads up, there is no real need to change the original query, as you can simply separate them and combine them after the fact. — Ph1reman
– Ph1reman, Commented Mar 22, 2022 at 11:49
Provide sample source data as CREATE TABLE (skip excess columns) + INSERT INTO (7-10 rows) and desired output for this data. Hint: join 2 copies of attribute table. — Akina
– Akina, Commented Mar 22, 2022 at 11:58

Rinkesh P · Accepted Answer · 2022-03-22 13:59:33Z

1

This is unrelated but maybe you want to change you change your table structures a bit, keep seperate lookups for size, colour and other attributes. But for now a dynamic pivot can solve your issue. Basically this is conditionally transforming your rows to columns, avoding extra joins. This can easily be extended to seperate other class of attributes into more colums by just adding the needed MAX(CASE statement

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=45c1fd5bac7382b0aa121c107267e9e1

answered Mar 22, 2022 at 13:59

Rinkesh P

7227 silver badges18 bronze badges

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

5 Comments

n.h. Over a year ago

Thank you. Unfortunately, I cannot change the table structure.

n.h. Over a year ago

Can you give me some hints on how can I get, if it is possible, the IN() populated based on psfr_attribute and psfr_attribute_group tables? Thank you!

Rinkesh P Over a year ago

it really depends on the context, at this point the thing that seperates colours and sizes in that column is that we know what colours and sizes are. One can argue that even BIG is a size which could be stored as B in sizes, then you would need to dynamically handle the B as well to be included in sizes. Unless you have a mapping (like an extra column attribute_type) to differentiate the values, I see no way you can differentiate them. You can prefix attributes with 'size_' or 'colour_' and use regular expressions to escape the extra column but that would be very inefficient as data increases.

n.h. Over a year ago

Knowing that sizes belong to id_attribute_group=1 made a subquery inside IN() and it is working. Think this is not an efficient way of doing it since I am duplicating requests. Is there another way? dbfiddle.uk/…

Rinkesh P Over a year ago

it wouldn't scale well, extra joins would drag the performance down and I didn't see any indexes on the tables, so it probably does a full table scan, which is bad. But for the current instance of the schema and as far as logic is considered, it will work.

Akina · Accepted Answer · 2022-03-22 13:26:21Z

1

SELECT pa.id_product_attribute,
       al1.name size,
       al2.name color
FROM psfr_product_attribute pa
JOIN psfr_product_attribute_combination pac1 ON pa.id_product_attribute = pac1.id_product_attribute
JOIN psfr_attribute_lang al1 ON pac1.id_attribute = al1.id_attribute
JOIN psfr_product_attribute_combination pac2 ON pa.id_product_attribute = pac2.id_product_attribute
JOIN psfr_attribute_lang al2 ON pac2.id_attribute = al2.id_attribute
WHERE pa.id_product = 59
  AND al1.id_attribute IN (1,2,3,4)
  AND al2.id_attribute IN (10,11)
  AND al1.id_lang = 1
  AND al2.id_lang = 1
  AND pa.quantity > 0

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2bdb34596360be343171bf90d8ca4849

answered Mar 22, 2022 at 13:26

Akina

43.2k6 gold badges17 silver badges30 bronze badges

2 Comments

n.h. Over a year ago

Thank you. How can I change the query in a way that the IN (1,2,3,4) and IN (10,11) could be filled dynamically? Because the psfr_attribute table will increase over time.

Akina Over a year ago

@n.h. Create attribute categories column in psfr_attribute_lang (1 - size, 2 - color, etc., maybe as a reference to categories table) and use it in WHERE instead of separate attributes list.

Ph1reman · Accepted Answer · 2022-03-22 11:45:02Z

There are several ways of doing this, but at the end of the day it's hard to give you the best sollution without seeing the original tables. As such, we cannot see if there are any Id's separating the types of attributes, or any other kind of category.

But, simply put. Assuming that you only have up to XL, and not XXL - then you can simply count the amount of characters so that if it is 2 or less, it's a size, if it's 3 or more, it's a color.

Then, you can join these two categories together. If that is not possible due to the sizes being above XL, simply define and "Where al.´name´ in ('XS', 'S', 'M', 'L', 'XL', 'XXL') - tag that as a size, then do an complete oposite (where not in) and join both these columns on the first row you show, the ID.

Here is an example:

CREATE TABLE #list (
Id int,
Attribute varchar(255),
)

insert into #list  (Id, Attribute)
Values (195, 'M'),
(195, 'RED'),
(197, 'L'),
(197, 'RED'),
(199, 'XL'),
(199, 'RED'),
(200, 'XL'),
(200, 'BLACK'),
(201, 'S'),
(201, 'RED'),
(202, 'S'),
(202, 'BLACK');


--Start from here, everything above is to fit your example data
select *
into #first
from #list a
where a.Attribute in ('XS', 'S', 'M', 'L', 'XL', 'XXL')

select a.Id, a.Attribute as 'Color', b.Attribute as 'Size'
from #list a
join #first b
on a.Id=b.Id and b.Attribute!=a.Attribute

drop table #list
drop table #first

Collectives™ on Stack Overflow

Combine rows with MySQL query

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related