Retrieve multiple foreign fields as aggregate field?

[tarsus]

I hope the thread subject conveys some semblance of what I'm asking. In my data structures I very frequently have a situation like this example:

Table "people":
field "personId"
field "name"

Table "people_hobbies":
field "personID"
field "hobby"

(In real life, there would actually be a "hobbies" table and "people_hobbies" would contain two foreign keys, but I'm keeping it simple as an example.)

What I want is to make a single query and get all people's names and a comma-delimited list of their hobbies. I.e.:

Name          Hobbies
Charlie         Reading, Art
Karen          Scuba Diving, Rock Climbing, Horseback Riding
Dave           Video Games, Movies

Clearly, the first step is the GROUP BY the personID. But I know of no aggregate function that will serve the purpose of concatenating the "hobby" field from all the records associated with a particular personID. CONCAT does not work like this; likewise, there are several aggregate functions that will pull one select value from the foreign records (like MIN and MAX), but none I can see that will combine them.

What I always must do, then, is use a query that retrieves one record per hobby (in this example):

SELECT p.name, h.hobby FROM people p LEFT JOIN people_hobbies h ON p.personID=h.personID ORDER BY p.personID

And then use programming logic to do one loop through the results and build my own array with the foreign field concatenated.

Anyone know of a way to do this with one query?

[tarsus]

Hold it! Turns out there is an aggregate function to do exactly that: GROUP_CONCAT. I could swear I've searched up and down for this before; how did I not know?

Oh well, perhaps I'll save someone else the trouble:

SELECT p.name, GROUP_CONCAT(h.hobby SEPARATOR ", ") AS hobbies
FROM people p LEFT JOIN people_hobbies h ON p.personId=h.personId
GROUP BY p.personID
ORDER BY p.name

[KLynch0803]

Tarsus,

I'm kinda new with this can you possibly help me with my query the same as yours? I'm not sure where to put some items in the query to make this work for my fields.. Thank you in advance.

http://www.webdeveloper.com/forum/sh...d.php?t=223444