Issue
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);
Solution
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 By – Rinkesh P
Answer Checked By – David Marino (BugsFixing Volunteer)