SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
Hybrid View
-
Aug 19, 2007, 05:41 #1
- Join Date
- Jun 2004
- Location
- Norway
- Posts
- 9
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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 | +---------------------------------------+
-
Aug 19, 2007, 07:08 #2
- 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.
-
Aug 19, 2007, 11:24 #3
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
sorry, CONCAT_WS in not right
you want to use GROUP_CONCAT instead
-
Nov 29, 2009, 15:52 #4
- 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.
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.
-
Nov 29, 2009, 16:22 #5
- Join Date
- Jul 2006
- Location
- Augusta, Georgia, United States
- Posts
- 4,194
- Mentioned
- 17 Post(s)
- Tagged
- 5 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.
http://dev.mysql.com/doc/refman/5.0/...functions.html
-
Nov 29, 2009, 16:31 #6
- 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