Given the test database from MySQL many-to-many join test and the following query:
mysql> select posts.*, categories.* from posts_categories join posts on posts_categories.post_id = posts.post_id join categories on posts_categories.category_id = categories.category_id;
+---------+----------+-------------+--------------+
| post_id | name | category_id | name |
+---------+----------+-------------+--------------+
| 1 | post one | 1 | category one |
| 1 | post one | 2 | category two |
| 2 | post two | 2 | category two |
+---------+----------+-------------+--------------+
3 rows in set (0.00 sec)
You can e.g. group and concatenate the categories:
mysql> select posts.*, categories.category_id, group_concat(categories.name), categories.* from posts_categories join posts on posts_categories.post_id = posts.post_id join categories on posts_categories.category_id = categories.category_id group by categories.name;
+---------+----------+-------------+-------------------------------+-------------+--------------+
| post_id | name | category_id | group_concat(categories.name) | category_id | name |
+---------+----------+-------------+-------------------------------+-------------+--------------+
| 1 | post one | 1 | category one | 1 | category one |
| 2 | post two | 2 | category two,category two | 2 | category two |
+---------+----------+-------------+-------------------------------+-------------+--------------+
2 rows in set (0.00 sec)
To specify the separator:
mysql> select posts.*, categories.category_id, group_concat(categories.name separator ' : '), categories.* from posts_categories join posts on posts_categories.post_id = posts.post_id join categories on posts_categories.category_id = categories.category_id group by categories.name;
+---------+----------+-------------+-----------------------------------------------+-------------+--------------+
| post_id | name | category_id | group_concat(categories.name separator ' : ') | category_id | name |
+---------+----------+-------------+-----------------------------------------------+-------------+--------------+
| 1 | post one | 1 | category one | 1 | category one |
| 2 | post two | 2 | category two : category two | 2 | category two |
+---------+----------+-------------+-----------------------------------------------+-------------+--------------+
2 rows in set (0.00 sec)
