SQL query to concatenate multiple rows into one column with MySQL GROUP_CONCAT

28/02/2020 - MYSQL

In this example we are going to find out which orders contain certain items. It is easy! However, we want to avoid returning multiple rows. Instead, we are going to concatenate order IDs with a comma sign and store them in a field.


Database


Tables


mysql> DESCRIBE items;
+-------+------------------------+------+-----+---------+----------------+
| Field | Type                   | Null | Key | Default | Extra          |
+-------+------------------------+------+-----+---------+----------------+
| id    | int(11) unsigned       | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100)           | NO   |     |         |                |
| price | decimal(11,2) unsigned | NO   |     | NULL    |                |
+-------+------------------------+------+-----+---------+----------------+

mysql> DESCRIBE orders;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| created_at | datetime         | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

mysql> DESCRIBE orders_items;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| orders_id | int(11) unsigned | NO   | MUL | NULL    |                |
| items_id  | int(11) unsigned | NO   | MUL | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

Data


mysql> SELECT * FROM items;
+----+----------+----------+
| id | name     | price    |
+----+----------+----------+
|  1 | bmw      | 20000.00 |
|  2 | mercedes | 25000.00 |
|  3 | audi     | 19000.00 |
|  4 | toyota   | 15000.00 |
|  5 | mazda    | 13000.00 |
+----+----------+----------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM orders;
+----+---------------------+
| id | created_at          |
+----+---------------------+
|  1 | 2010-01-01 00:00:00 |
|  2 | 2010-02-01 00:00:00 |
|  3 | 2010-03-01 00:00:00 |
+----+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM orders_items;
+----+-----------+----------+
| id | orders_id | items_id |
+----+-----------+----------+
|  1 |         1 |        1 |
|  2 |         1 |        2 |
|  3 |         1 |        3 |
|  4 |         2 |        1 |
|  5 |         3 |        1 |
|  7 |         3 |        4 |
| 10 |         2 |        5 |
+----+-----------+----------+
7 rows in set (0.00 sec)

Query


mysql> SELECT
    -> items.name AS ItemName,
    -> GROUP_CONCAT(orders.id SEPARATOR ',') AS Orders
    -> FROM orders
    -> INNER JOIN orders_items ON orders.id = orders_items.orders_id
    -> INNER JOIN items ON orders_items.items_id = items.id
    -> WHERE items.name = "bmw" OR items.name = "mercedes" OR items.name = "audi"
    -> GROUP BY items.id;

Result


+----------+--------+
| ItemName | Orders |
+----------+--------+
| bmw      | 2,1,3  |
| mercedes | 1      |
| audi     | 1      |
+----------+--------+
3 rows in set (0.00 sec)