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 entryincategory 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.