How to concatenate values from joined table in specific order, mysql

Question

Regarding to my last question, I run into problem of sorting joined values.

In this example, I have a table with offers which is not important in this question, but it is main table:

Table offers
----------------------------
id  title            price
----------------------------
1   Offer 1 title    300 Eur
2   Offer 2 title    250 Eur
3   Offer 3 title    350 Eur

There is a list of department cities:

Table departments
----------------------------
id  name
----------------------------
1   London
2   Leeds
3   Manchester
4   Glasgow

and relation table with offer table

Table rel_departments
------------------------
id  offer_id  rel_id
------------------------
1   1         1
2   1         3
3   1         2
4   2         1
5   3         4

When running query to get concatenated department cities name, I got the result. However, this result is not in good order.

SELECT `offers`.id, 
GROUP_CONCAT(`departments`.`name` SEPARATOR ', ') AS departures
FROM `offers`
INNER JOIN `rel_departments` ON (`rel_departments`.`offer_id` = `offers`.id)
INNER JOIN `departments` ON (`rel_departments`.`rel_id` = `departments`.id)
GROUP by offers.id

I got result:

id  departures
----------------------------------------
1   London, Manchester, Leeds
2   London
3   Glasgow

This is OK, but I need to sort them in (in this way descending order by id in relation table) which means I need result to be (for the first offer id) Leeds, Manchester, London

I have tried everything but with no success. Any idea?

p.s. HERE is a sqlfiddle demo

score 1 · Accepted Answer · answered May 14 '17 at 16:02

You can use a ORDER BY in GROUP_CONCAT function.

Query

SELECT `offers`.id, 
GROUP_CONCAT(`departments`.`name` ORDER BY `offers`.id SEPARATOR ', ') AS departures
FROM `offers`
INNER JOIN `rel_departments` ON (`rel_departments`.`offer_id` = `offers`.id)
INNER JOIN `departments` ON (`rel_departments`.`rel_id` = `departments`.id)
GROUP by offers.id

Result

| id |                departures |
|----|---------------------------|
|  1 | Leeds, Manchester, London |
|  2 |                    London |
|  3 |                   Glasgow |

see demo http://www.sqlfiddle.com/#!9/ae7201/3

Yep, but actually I have to sort them by rel_departments.id but logic is the same and it works! thanks! — Војин Петровић, May 14 '17 at 16:08

How to concatenate values from joined table in specific order, mysql

1 Answers1