dracoblue.net

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

entry = id,title
category = id,name
entry_in_category = category_id, entry_id

I want to query for the following:

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.

SELECT 
	e.id, e.title,
	(
		SELECT <a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat">GROUP_CONCAT</a>(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:

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.