inanzzz | Merging specific column values from multiple rows into one with MySQL query

In this example we are going to list all customers with their associated accounts but in a single row rather than multiple.

Database

Tables

CREATE TABLE `customers` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `accounts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `customers_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_customers` (`customers_id`),
  CONSTRAINT `FK_customers` FOREIGN KEY (`customers_id`) REFERENCES `customers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Data

mysql> SELECT * FROM customers;
+----+------+---------------------+
| id | name | created_at          |
+----+------+---------------------+
|  1 | Bob  | 2010-01-01 00:00:00 |
|  2 | Joe  | 2010-01-01 00:00:00 |
|  3 | Leo  | 2010-01-02 00:00:00 |
|  4 | Edi  | 2009-01-01 00:00:00 |
+----+------+---------------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM accounts;
+----+---------+--------------+
| id | type    | customers_id |
+----+---------+--------------+
|  1 | Current |            1 |
|  2 | Current |            2 |
|  3 | ISA     |            1 |
|  4 | Savings |            4 |
+----+---------+--------------+
4 rows in set (0.00 sec)

Query

This is the query with multiple rows.

mysql> SELECT
    -> c.id,
    -> c.name,
    -> c.created_at,
    -> a.type
    -> FROM customers c
    -> LEFT JOIN accounts a ON c.id = a.customers_id
    -> ORDER BY c.created_at DESC;

+----+------+---------------------+---------+
| id | name | created_at          | type    |
+----+------+---------------------+---------+
|  3 | Leo  | 2010-01-02 00:00:00 | NULL    |
|  1 | Bob  | 2010-01-01 00:00:00 | Current |
|  2 | Joe  | 2010-01-01 00:00:00 | Current |
|  1 | Bob  | 2010-01-01 00:00:00 | ISA     |
|  4 | Edi  | 2009-01-01 00:00:00 | Savings |
+----+------+---------------------+---------+
5 rows in set (0.00 sec)

This is the merged version.

mysql> SELECT
    -> c.id,
    -> c.name,
    -> c.created_at,
    -> GROUP_CONCAT(a.type SEPARATOR ',') AS accounts
    -> FROM customers c
    -> LEFT OUTER JOIN accounts a ON c.id = a.customers_id
    -> GROUP BY c.id
    -> ORDER BY c.created_at DESC;

+----+------+---------------------+-------------+
| id | name | created_at          | accounts    |
+----+------+---------------------+-------------+
|  3 | Leo  | 2010-01-02 00:00:00 | NULL        |
|  1 | Bob  | 2010-01-01 00:00:00 | Current,ISA |
|  2 | Joe  | 2010-01-01 00:00:00 | Current     |
|  4 | Edi  | 2009-01-01 00:00:00 | Savings     |
+----+------+---------------------+-------------+
4 rows in set (0.01 sec)