MySQL GROUP_CONCAT function

I knew about this MySQL function called "GROUP_CONCAT" lately and I think it is really a very useful function to use

To make simple for you to understand, I will use a small example to illustrate on

Consider we have a table called listings which has many to many relationship with another table called geographies knowing that the join table name is listings_geographies

Now look at this query

select listings.id, listings.display_name, geographies.name as geography_name from listings LEFT OUTER JOIN listings_geographies ON listings.id = listings_geographies.listing_id LEFT OUTER JOIN geographies ON listings_geographies.geography_id = geographies.id WHERE status = 3 GROUP BY listings.id

In my example I have only two listings: the first has 3 geographies and the second has 2 geographies. By running this command, you will get 2 records but the field called "geography_name" will have one of the three values and not all the values

What if you want to grab all the three values and add them in 1 field so that you get 2 records but with "geography_name" field containing all geographies names values. If you want that, then you can rely on this nice function "GROUP_CONCAT"

The above function can be re-written to be this one

select listings.id, listings.display_name, GROUP_CONCAT(geographies.name SEPARATOR ' ') as geography_name from listings LEFT OUTER JOIN listings_geographies ON listings.id = listings_geographies.listing_id LEFT OUTER JOIN geographies ON listings_geographies.geography_id = geographies.id WHERE status = 3 GROUP BY listings.id

That's it
I needed this function as I was using Sphinx and I wanted to have a query written to grab all record info with its associations in 1 record in order to be indexed

Enjoy! :)