Devz Devz - 4 months ago 8
SQL Question

Aggregate results in one column for each row of an other request linked to the first one

Okay, so i know that my title is a bit blurry but it's actually pretty easy (i guess).

I'm using mysql.

I have a table 'items' with this structure:

id, name


And a table 'infos' such as :

id_item, info


The thing i'd like to do is to concat infos in such a way that with a single request i can have that result:

id: 0, name: example1, infos: info1, info2, info3
id: 1, name: example2, infos: null
id: 2, name: example1, infos: info1


I was thinking about something like this :

SELECT *, (SELECT info FROM infos WHERE id_item = ?) as infos FROM items


But i can't find the way to tell my inner request to be linked to the row that's being treated, and joining tables doesn't seem to be the right way to do it...

Can someone help me ? I feel that i'm so close !

Answer

Try GROUP_CONCAT() function:

SELECT id, name, group_concat(info)
FROM 
    items it
    JOIN infos in
        ON in.id_item = it.id       
GROUP BY id, name;