NARTONIC NARTONIC - 16 days ago 8
MySQL Question

Concat column MYSQL

I Have this select

SELECT page.*, page_lang.*, category.title as category FROM page
JOIN page_lang ON page.id = page_lang.id_page
JOIN relation ON page.id = relation.from_id
JOIN page_lang as category ON category.id_page = relation.to_id
WHERE page.type = 'blog-detail'
AND relation.type = 1


This return duplicate rows when one page have two or more categories

ID PAGE | TITLE | CATEGORIES


1 | Title 1 | category 1


2 | Title 2 | category 1


3 | Title 3 | category 2


1 | Title 1 | category 2

But I want this

ID PAGE | TITLE | CATEGORIES


1 | Title 1 | category 1, category 2


2 | Title 2 | category 1


3 | Title 3 | category 2

If I use GROUP_CONCAT(category.title) return 1 row.

Answer

You should use group_concat:

SELECT page.*, page_lang.*, group_concat(distinct category.title) as category FROM page
JOIN page_lang ON page.id = page_lang.id_page
JOIN relation ON page.id = relation.from_id
JOIN page_lang as category ON category.id_page = relation.to_id
WHERE page.type = 'blog-detail'
AND relation.type = 1
GROUP BY page.id
Comments