Retrieve multiple foreign fields as aggregate field?
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:
(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.:
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.