Devz Devz - 1 year ago 35
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 Source

Try GROUP_CONCAT() function:

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