How to use MySQL to return a concatenated string?

Question

I am using a custom built version of data tables to list records from tables. Most of the time it is wonderful but occasionally I need to join two or more tables to show specific data from lookup tables. Here is a new problem. I have 3 tables... event_categories, themes, and themes_eventcategories. event_categories and themes are normal tables and themes_eventcategories has the fields 'id', 'theme_id', and 'event_category_id'.

What I need to do is get a list of event categories and if there are any themes associated with the event category, I need the themes in a comma separated string. Is this possible?

This is possible, but do you use this query for production purposes? — Lefteris Bab
– Lefteris Bab, Commented Jul 29, 2013 at 18:36
It will be something we use on the back-end for administration so it is okay if it is a slow query. — pogeybait
– pogeybait, Commented Jul 29, 2013 at 18:49
It is not about speed. If you use concat and have lots of events, then there will be a problem. There is a certain amount of characters that you can use in that field. The best way is to use nested select — Lefteris Bab
– Lefteris Bab, Commented Jul 29, 2013 at 18:51
Also keep in mind that since I am using a custom version of datatables, I will need to pull data primarily from the event_categories table i.e. SELECT * FROM event_categories INNER JOIN themes_eventcategories .... etc etc — pogeybait
– pogeybait, Commented Jul 29, 2013 at 18:52

DarkAjax · Accepted Answer · 2013-07-29 18:34:20Z

1

What you want is GROUP_CONCAT, maybe something like:

SELECT event, GROUP_CONCAT(theme)
FROM themes_eventcategories a
JOIN event_categories b ON a.event_category_id = b.id
JOIN themes c ON a.theme_id = c.id
GROUP BY event

answered Jul 29, 2013 at 18:34

DarkAjax

16.3k11 gold badges60 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use MySQL to return a concatenated string?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related