How to use Group_concat to get a list of categories for each product?

Question

I have a multi language website based on OpenCart 3 which I've included the necessary tables and SQLFiddle for it down below.

I need a list of comma separated category name for each product, like this:
product_id, language_id, product_name
in which product_name is the comma separated list of categories. Example of Product Name: Nightgown, Wrap robes, 2li
which actually are categories: 8, 188, 192

Important Note: At the end, I want to fill the name column of product_description table with the comma separated list of categories I explained above.

It seems I should use GROUP_CONCAT, but it's far beyond my SQL knowledge even though I spend more than half a day for it.

Here is SQLFiddle: http://sqlfiddle.com/#!9/0337c3

Schema

CREATE TABLE `category` (
    `category_id` int(11) NOT NULL,
    `parent_id` int(11) NOT NULL DEFAULT '0'
) 

CREATE TABLE `category_description` (
    `cd_id` int(11) NOT NULL,
    `category_id` int(11) NOT NULL,
    `language_id` int(11) NOT NULL,
    `name` varchar(255) NOT NULL
) 

CREATE TABLE `language` (
    `language_id` int(11) NOT NULL,
    `name` varchar(32) NOT NULL,
    `code` varchar(5) NOT NULL,
    `locale` varchar(255) NOT NULL,
    `image` varchar(64) NOT NULL,
    `directory` varchar(32) NOT NULL,
    `sort_order` int(3) NOT NULL DEFAULT '0',
    `status` tinyint(1) NOT NULL
) 

CREATE TABLE `product` (
    `product_id` int(11) NOT NULL,
    `model` varchar(64) NOT NULL,
    `price` decimal(15,4) NOT NULL DEFAULT '0.0000'
) 

CREATE TABLE `product_description` (
    `product_id` int(11) NOT NULL,
    `language_id` int(11) NOT NULL
) 

CREATE TABLE `product_to_category` (
    `product_id` int(11) NOT NULL,
    `category_id` int(11) NOT NULL
) 


ALTER TABLE `category`
    ADD PRIMARY KEY (`category_id`,`parent_id`),
    ADD KEY `parent_id` (`parent_id`);

ALTER TABLE `category_description`
    ADD PRIMARY KEY (`cd_id`,`category_id`,`language_id`),
    ADD KEY `name` (`name`);

ALTER TABLE `language`
    ADD PRIMARY KEY (`language_id`),
    ADD KEY `name` (`name`);

ALTER TABLE `product`
    ADD PRIMARY KEY (`product_id`);

ALTER TABLE `product_description`
    ADD PRIMARY KEY (`product_id`,`language_id`),
    ADD KEY `name` (`name`);

ALTER TABLE `product_to_category`
    ADD PRIMARY KEY (`product_id`,`category_id`),
    ADD KEY `category_id` (`category_id`);


ALTER TABLE `category`
    MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=202;
ALTER TABLE `category_description`
    MODIFY `cd_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=961;
ALTER TABLE `language`
    MODIFY `language_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
ALTER TABLE `product`
    MODIFY `product_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=85;

I'd appreciate if you tell me what's the problem?

Answer 1

You can use the following select statement:

select
    pd.product_id,
    pd.language_id,
    group_concat(cd.name order by cd.language_id separator ', ') as product_name
from product_description pd
left join product_to_category pc using (product_id)
left join category_description cd using (category_id, language_id)
group by pd.product_id, pd.language_id

I don't think it's a good idea to store redundat data in a new column. But you can do it with:

update product_description pd
join (
  select
      pd.product_id,
      pd.language_id,
      group_concat(cd.name order by cd.language_id separator ', ') as product_name
  from product_description pd
  left join product_to_category pc using (product_id)
  left join category_description cd using (category_id, language_id)
  group by pd.product_id, pd.language_id
) sub using (product_id, language_id)
set pd.name = sub.product_name;