[SOLVED] Concating results as one field

[asmith]

Hi,

 

Just wondering if something like this is possible:

 

SELECT field1 FROM table1, table3.member as membersField

INNER JOIN (SELECT member FROM table2 ON table1.ID = table2.ID) as table3 ...

 

So that instead of getting multiple rows as each member, it concat member filed in to one for each row of table1.

 

Something like this: (For result)

 

field1    membersField

d1        tom, john, alex

d2        richard, jack

 

instead of

d1  tom

d1  john

d1  alex

d2  richard

d2  jack

 

Is it possible?

[kickstart]

Hi

 

The function you need is GROUP_CONCAT

 

Something like this

 

SELECT a.field1, GROUP_CONCAT(b.membersField SEPARATOR ' ')

FROM table1 a

INNER JOIN table2 b

ON a.ID = b.ID

ORDER BY a.field1

 

All the best

 

Keith

 

[asmith]

Thanks a lot mate

 

it is what I need.

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

[fenway]

Just be careful -- there's a maximum length to group-concat which is very short by default.