MerkisL MerkisL - 2 months ago 8
MySQL Question

Complex concatenate in MySQL query

I'm having issues to concatenate my MySQL results the way I needed, so small help would be appreciated.

I have tree tables - posts, categories, posts_categories:

posts:

+----------+
| Field |
+----------+
| id |
| title |
| body |
| date |
+----------+


categories:

+---------------+
| Field |
+---------------+
| id |
| category_name |
+---------------+


posts_categories:

+-------------+
| Field |
+-------------+
| id |
| post_id |
| category_id |
+-------------+


My current MySQL query that gets all category names for posts:

SELECT
posts.*, categories.category_name
FROM
posts,
categories,
posts_categories
WHERE
posts_categories.category_id = categories.id
AND posts.id = posts_categories.post_id;


Result of query above:

id | title | posts.category_name
0 title1 category1
0 title1 category3
0 title1 category6
1 title2 category9


However I'd like to concatenate category_name, so that my MySQL query results shows all category names for each post in one line. Lke this:

id | title | posts.category_name
0 title1 category1, category3, category6
1 title2 category9


How would I do this?

Answer

Group by your posts columns and use group_concat() to get a list of the categories

SELECT posts.id, posts.title, 
       group_concat(categories.category_name separator ', ')
FROM posts 
LEFT JOIN posts_categories ON posts.id = posts_categories.post_id
LEFT JOIN categories ON posts_categories.category_id = categories.id
GROUP BY posts.id, posts.title

BTW do not use the old join syntax anymore. Use the explicit JOIN sytax like in my query.