dracoblue.net

Concat multiple references as one imploded column (mysql)

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.

In mysql, open source by
@ 21 Feb 2009, Comments at Reddit & Hackernews

Give something back

Were my blog posts useful to you? If you want to give back, support one of these charities, too!

Report hate in social media Campact e.V. With our technology and your help, we protect the oceans from plastic waste. Gesellschaft fur Freiheitsrechte e. V. The civil eye in the mediterranean

Recent Dev-Articles

Read recently

Recent Files

About