1

I am trying to write an SQL query to pull data from two tables, and would like to combine several results in a single row, as in:

+---------------------+----------------------------------+
| name                | numbers                          |
+---------------------+----------------------------------+
| Bob Doe             | N/A                              |
| Mary Jones          | +8 213 621 0002,+6 800 444 4444  |
| Anna Smith          | +4 71 793 336                    |
| Sami Doe            | +555 50 333 3333                 |
| John Smith          | +3 604 222 4444,+44 20 8759 9036 |
+---------------------+----------------------------------+

As you can see, Mary Jones has two numbers, etc. This is the query I have:

SELECT CONCAT_WS(' ', people.first_name, people.last_name) AS name,
COALESCE(phones.number, 'N/A') AS numbers
FROM people
LEFT JOIN phones
        ON phones.user_id = people.id;

I've been trying to use GROUP BY, but I'm not sure how to incorporate it into my query.

The schema looks something like this:

mysql> select * from people;
+----+------------+-------------+
| id | first_name | last_name   |
+----+------------+-------------+
|  1 | John       | Smith       |
|  2 | Mary       | Jones       |
|  3 | Bob        | Doe         |
|  4 | Sami       | Doe         |
|  5 | Anna       | Smith       |
+----+------------+-------------+

mysql> select * from phones;
+----+---------+------------------+
| id | user_id | number           |
+----+---------+------------------+
|  1 |       2 | +1 213 621 0002  |
|  2 |       2 | +1 800 444 4444  |
|  3 |       1 | +1 604 444 4444  |
|  4 |       1 | +44 20 8759 9036 |
|  5 |       4 | +358 50 333 3333 |
+----+---------+------------------+
Bobby Wan-Kenobi
  • 885
  • 9
  • 18
  • I believe you are looking for aggregate function [group_concat()](https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat). – JNevill Jan 31 '23 at 15:32
  • Ultimately this will look something like: `SELECT CONCAT_WS(' ', people.first_name, people.last_name) AS name, GROUP_CONCAT(phones.number SEPARATOR ',') AS numbers FROM people LEFT JOIN phones ON phones.user_id = people.id GROUP BY name;` – JNevill Jan 31 '23 at 15:36
  • 1
    @JNevill I believe you are right, changed it – RiggsFolly Jan 31 '23 at 15:38
  • @JNevill but that way I won't get N/A for users that have no number, like Bob Doe, for example. Also, I get the error `this is incompatible with sql_mode=only_full_group_by` – Bobby Wan-Kenobi Jan 31 '23 at 15:40
  • For the `ONLY_FULL_GROUP_BY` issue, make sure all columns in your `SELECT` clause that aren't being aggregated by an aggregation formula like `sum()` or `group_concat()` or what-have-you, are present in your `GROUP BY` clause. You may have to swith out the `name` alias to the full formula in the `GROUP BY`: `CONCAT_WS(' ', people.first_name, people.last_name)` As for `N/A` you can wrap your `GROUP_CONCAT()` function in `COALESCE()` like you had originally planned. `COALESCE(GROUP_CONCAT(phones.number SEPARATOR ','), 'N/A') AS numbers` should do the trick. – JNevill Jan 31 '23 at 16:23

0 Answers0