CODEWITHSUNDEEP
mysqlconcatenationgroup-concat
NARTONIC
NARTONIC

Reputation: 472

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.

Upvotes: 0

Views: 52

Answers (2)

moz programmer
moz programmer

Reputation: 1

try using the command "UNIQUE"

SELECT UNIQUE 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

Upvotes: 0

2oppin
2oppin

Reputation: 2001

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

Upvotes: 1

Related Questions