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);
CodePudding user response：
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
CodePudding user response：
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
CodePudding user response：
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