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

Answer 2

You're making an ambiguous subselect statement on the email_address table. In other words: you're trying to select multiple rows in your subselect for a single row selection. Think about that and I'll ask you this question: "How does the database decide, with your query, which email address to select?" The answer is that it can't, because your query doesn't specify which email address to use. Hence your error.

You could limit your subselection to 1 value. Here is how that might look:

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

Still, your best option might be to email all of the potential emails. To this effect, you need a JOIN clause that might be structured something like this:

SELECT usr.name AS name, addr.email AS email
FROM users AS usr
LEFT JOIN email_address AS addr ON (addr.user_id=usr.id);

That will give you a selection of rows that is a combination of users and email addresses. Meaning you will have multiple rows returned for any user which has more than one email address association. That was you can iterate through the all the returned rows and email all the user's emails. If there is some way to identify a primary email address, then you'd probably just email the primary.

Answer 3

I'd rather do like this instead of using subquery

    select
    GROUP_CONCAT(ea.email_address) as emails,
    u.name
    from 
    users u 
    left join email_address ea ON (u.id=ea.user_id)
    group by u.id,u.name