SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Jun 2004
    Location
    Norway
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Concatenate results into a single field

    Say I have the two tables listed below in my database.

    Code:
    Table #1:  persons
    +----------------+
    | id | name      |
    +----+-----------+
    | 23 | Oluf      |
    | 24 | Christian |
    | 25 | Jenny     |
    +----+-----------+
    
    
    Table #2:  person_pets
    +----------------------+
    | person_id | name     |
    +-----------+----------+
    | 23        | Fido     |
    | 23        | Garfield |
    | 23        | Casper   |
    | 25        | Shadow   |
    +-----------+----------+


    Now, how can I construct a query which yields the results like this,
    where the persons' pets are listed in a single field, separated with
    a comma (or any other chosen character). Is it possible?

    Code:
    +---------------------------------------+
    | id | name      | pet_names            |
    +---------------------------------------+
    | 23 | Oluf      | Fido,Garfield,Casper |
    | 24 | Christian |                      |
    | 25 | Jenny     | Shadow               |
    +---------------------------------------+

  2. #2
    SitePoint Wizard gold trophysilver trophybronze trophy dc dalton's Avatar
    Join Date
    Nov 2004
    Location
    Right behind you, watching, always watching.
    Posts
    5,431
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could try CONCAT_WS(). I'd be kind of curious why you want to do this.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    sorry, CONCAT_WS in not right

    you want to use GROUP_CONCAT instead

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd be kind of curious why you want to do this.
    To make a database VIEW containing potentially ephemeral and complex information that would otherwise be a pain or a waste of CPU to do programmatically from the resulting dataset.

    In my case I have several tables joined together into a VIEW. Data from each table can be viewed separately without the VIEW as unque entity.

    The VIEW itself represents a more specific meta concept when the various tables are combined.

    I store a 'slug' (url safe string for friendly urls) with each record in each table. In order to make a 'slug' or 'path' from the data in the view means combining all the strings and ids from each table that is in the view. I was just doing that in my server side code but when I started doing some complex grouping output of data looping through arrays, all the necessary data was not readily available.

    Then it hit me, that I could just let MySQL give me that information from the start since it has all the information ready to go.

    So I added a new virtual 'slug' to my view and now I don't have to manually concatenate that information from the result set.

    I know I revived an older topic, but this post came up when I was searching for MySQL CONCAT & CONCAT_WS functions. And in my most recent project I have found so much help and gotten so many ideas from generous folks in forums I thought I would share why I'm doing it to hopefully inspire others to find solutions.

    merry day.

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    You may end up with truncated data when using GROUP_CONCAT() due to the 1024 default character limit. That is why many times it is best to handle this type of logic within the application language unless your certain the returned data will not go beyond 1024 characters.

    MySQL Manual
    The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024.
    Given very sensitive data using GROUP_CONCAT() could result in a disaster if you don't consider that it will truncate data that goes beyond the default limit unless you increase it manually.

    http://dev.mysql.com/doc/refman/5.0/...functions.html

  6. #6
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the heads up oddz.

    Though I am not currently using GROUP_CONCAT (group_by function), only CONCAT and CONCAT_WS (string functions).

    It does explicitly say in the MySQL documentation that there is a limit to the results when using the string concatenation functions but I will bear that in mind if it comes up. In my case I am combining 4 strings (100 characters max each) and an id (which I could only hope would become long enough to be a problem

    Hopefully someone finds this information useful or inspiring. I've only recently started to exploit the deeper powers and potential of SQL functions and it is changing my life


Bookmarks

Posting Permissions

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