How to overcome MySQL 'Subquery returns more than 1 row' error and select all the relevant records

Question

Table creation

CREATE TABLE `users` (
  `id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  PRIMARY KEY(`id`)
);

CREATE TABLE `email_address` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `email_address` VARCHAR(50) NOT NULL,
  INDEX pn_user_index(`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES users(`id`) ON DELETE CASCADE,
  PRIMARY KEY(`id`)
);

Data insertion

INSERT INTO users (id, name) VALUES (1, 'Mark'), (2, 'Tom'), (3, 'Robin'); 

INSERT INTO email_address (user_id, email_address) VALUES 
(1, 'mark@gmail.com'), (1, 'mark@yahoo.com'), (1, 'mark@msn.com'), 
(2, 'tom@gmail.com'), (2, 'tom@yahoo.com'), (2, 'tom@msn.com'),
(3, 'robin@gmail.com'), (3, 'robin@yahoo.com'), (3, 'robin@msn.com');

SQL query

SELECT usr.name AS name
     , (SELECT email.email_address 
          FROM email_address AS email 
       WHERE email.user_id = usr.id) AS email 
  FROM users AS usr;    

With the use of above my MySQL query, how can I avoid the MySQL Error 'Subquery returns more than 1 row' and select the all the relevant email address for particular User as below. Thanks.

+----------+-------------------------------------------------+
|   name   |                     email                       |
+----------+-------------------------------------------------+
|   Mark   | mark@gmail.com, mark@yahoo.com, mark@msn.com    |
|   Tom    | tom@gmail.com, tom@yahoo.com, tom@msn.com       |
|   Robin  | robin@gmail.com, robin@yahoo.com, robin@msn.com |                   
+----------+----------+--------------------------------------+

Answer 1

GROUP_CONCAT with SEPARATOR and simplify your query a bit:

SELECT users.name AS name,
(SELECT GROUP_CONCAT(email_address.email_address SEPARATOR ', ')
FROM email_address
WHERE email_address.user_id = users.id) AS email
FROM users

Reference: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat