Results 1 to 3 of 3

Thread: Retrieve multiple foreign fields as aggregate field?

  1. #1
    Join Date
    Mar 2005

    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:

    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?
    Last edited by tarsus; 11-03-2009 at 03:25 PM.

  2. #2
    Join Date
    Mar 2005
    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

  3. #3
    Join Date
    Jan 2010

    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.


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center