Get column from children as list in one column

Question

I have one table called forms and one table called categories. They have a many-to-many relationship through a category_form as pivot. The pivot table has form_id and category_id

So each form can have many categories and every category can be attached to many forms.

I need to aggregate this data somehow. What I need is a comma separated list of category id's in a column for each form.

Like this:

FORMS
id  | name | categories 
1   | f1   | 1,4,7
2   | f2   | 1
3   | f3   | 1,6,8,9

What would be an effective way of doing this?

Please share table structure with sample data for forms, categories and category_form. And also please mention the version of mysql you are using. — Kazi Mohammad Ali Nur Romel, Mar 27 '21 at 13:58

score 1 · Accepted Answer · answered Mar 27 '21 at 14:01

1

Try this:

SELECT f.id, f.name, GROUP_CONCAT(cf.category_id) AS categories
FROM forms f
INNER JOIN category_form cf ON f.id = cf.form_id
GROUP BY f.id, f.name
ORDER BY f.id;

answered Mar 27 '21 at 14:01

nachospiu

2,009
2
8
12

Get column from children as list in one column

1 Answers1