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