In my blog I have the following structure (short version):

1
2
3
entry = id,title
category = id,name
entry_in_category = category_id, entry_id

I want to query for the following:

1
2
3
4
id | title | categories
---------------------------------
1 | My Entry | php,mysql
2 | Second ... | mysql,java

So if you want to get all entries with the corresponding categories you may do a refetch on each of the entries id in the entry_in_category table. But if (for example for tags) you really just want to have the name of the categories and nothing more, here comes a pretty gentle way to solve that issue.

1
2
3
4
5
6
7
8
9
SELECT
e.id, e.title,
(
SELECT GROUP_CONCAT(c.name)
FROM entry_in_category e_i_c
LEFT JOIN category c ON c.id=e_i_c.category_id
WHERE e_i_c.entry_id = e.id
) categories
FROM entry e

This solution uses the GROUP_CONCAT function, which was intruduced in mysql 4.1 and concats all results name-fields (oviously not grouped by anything) into a single string.

By default mysql concats the entries by using a ',' but you may use a different seperator by replacing the group-concat-part with the following:

1
GROUP_CONCAT(c.name SEPARATOR '-')

Thanks to Christian for bringing up a similiar issue, hope that solution helps you to get an idea on how to deal with similiar cases.