How do I concatenate strings from a subquery into a single row in mysql?

Question

I have three tables:

table "package" ----------------------------------------------------- package_id      int(10)        primary key, auto-increment package_name    varchar(255) price           decimal(10,2)  table "zones" ------------------------------------------------------ zone_id         varchar(32)    primary key (ex of data: A1, Z2, E3, etc)  table "package_zones" ------------------------------------------------------ package_id     int(10) zone_id        varchar(32) 

What I'm trying to do is return all the information in package table PLUS a list of zones for that package. I want the list of zones sorted alphabetically and comma separated.

So the output I'm looking for is something like this...

+------------+---------------+--------+----------------+ | package_id | package_name  | price  | zone_list      | +------------+---------------+--------+----------------+ | 1          | Red Package   | 50.00  | Z1,Z2,Z3       | | 2          | Blue Package  | 75.00  | A2,D4,Z1,Z2    | | 3          | Green Package | 100.00 | B4,D1,D2,X1,Z1 | +------------+---------------+--------+----------------+ 

I know I could do something in PHP with the presentation layer to get the desired result. The problem is, I would like to be able to sort zone_list ASC or DESC or even use" WHERE zone_list LIKE" and so on. In order to do that, I need this done in MYSQL.

I have NO idea how to even begin to tackle this. I tried using a subquery, but it kept complaining about multiple rows. I tried to concat the multiple rows into a single string, but evidently MySQL doesn't like this.

Thanks in advance.

UPDATE!

Here is the solution for those who are interested.

SELECT      `package`.*,     GROUP_CONCAT(`zones`.`zone` ORDER BY `zones`.`zone` ASC SEPARATOR ','  )  as `zone_list` FROM      `package`,     `package_zones` LEFT JOIN      (`zones`,`ao_package_zones`) ON (`zones`.`zone_id` = `package_zones`.`zone_id` AND `package_zones`.`package_id` = `package`.`package_id`) GROUP BY      `ao_package`.`package_id` 

Answer 1

by using the GROUP_CONCAT() function and a GROUP BY call. here's an example query

SELECT     p.package_id,    p.package_name,    p.price,    GROUP_CONCAT(pz.zone_id SEPARATOR ',') as zone_list  FROM     package p  LEFT JOIN package_zone pz ON p.package_id = pz.package_id  GROUP BY     p.package_id 

you should still be able to order by zone_id s (or zone_list), and instead of using LIKE, you can use WHERE zp.zone_id = 'Z1' or something similar.