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`

Tentative

use group by with group_concat on zone field – Ellene

I used your question updated part select roles.*, GROUP_CONCAT(rp.permission_id SEPARATOR ',') as permission_ids from roles join role_permissions rp on roles.id = rp.role_id GROUP BY roles.id. works – Latreshia

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.

Eightfold

So close. Almost there! I'd like zone_list to be sorted alphabetically. That's why I was thinking more along the lines of a subquery. Very close though. – Tentative

Figured out I could put "ORDER BY" in GROUP CONCAT. so GROUP_CONCAT(pz.zone_id ORDER BY pz.zone_id ASC SEPARATOR ',') worked!!!! – Tentative

Revision: did not work. It is always returning all the possible zones for each row. Each row should only return the zones for that specific package. – Tentative

I figured out how to prevent the return of all possible rows. You had to do a cross join. I will put the answer in the queestion for all to see. – Tentative

For anyone how wants to do it in SQL Server: #7399 – Secondrate

Perfect... needed to add the group by to specify which column I wanted to group by, otherwise it defaults to the column you are group_concat ing! – Appreciate

Answer 2

I have a customer who requested me to generate a query to use in a prestashop database to get the last year records from ps_orders, ps_customer tables; and one of those column was "The list of product ids in the order" from the ps_order_detail table. So, my query result was:

SELECT 
    o.id_order, o.reference, 
    (
        SELECT 
            GROUP_CONCAT(od.product_id SEPARATOR ', ') AS products 
        FROM ps_order_detail od 
        WHERE od.id_order = o.id_order 
        GROUP BY od.id_order
    ) AS product_ids,
    CONCAT(c.firstname, ' ', c.lastname) AS customer, o.current_state, o.payment, o.total_discounts, 
    o.total_discounts_tax_incl, o.total_discounts_tax_excl, o.total_paid, o.total_paid_tax_incl,
    o.total_paid_tax_excl, o.total_products, o.total_products_wt, o.total_shipping, o.total_shipping_tax_incl, 
    o.total_shipping_tax_excl, o.carrier_tax_rate, o.total_wrapping, o.total_wrapping_tax_incl, 
    o.total_wrapping_tax_excl, o.invoice_number, o.delivery_number, o.valid
FROM ps_orders o
INNER JOIN ps_customer c ON c.id_customer = o.id_customer
WHERE 
    o.date_add <= NOW() AND o.date_add >= DATE_ADD(NOW(), INTERVAL - 12 MONTH);

Result:

Anthropomorphic