By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table customer_info (ID int, Name varchar(30));
create table phone_numbers (ID int, phone varchar(30), customerID int, ext int, notes varchar(30));
insert into customer_info (ID, Name) values
(1,'John'),
(2,'jill');
insert into phone_numbers (ID, phone, customerID, ext, notes) values
(1,'687-5309',1,10,'Note1'),
(2,'687-5310',1,20,'Note2'),
(3,'687-5311',1,null,'Note3'),
(4,'235-1189',2,40,'Note4'),
(5,'235-2324',2,50,'Note5');
SELECT cust.*, phone.ID AS phone_id, phone.phone, phone.ext, phone.notes
FROM customer_info AS cust
LEFT JOIN phone_numbers AS phone ON phone.customerID = cust.ID
WHERE cust.ID = 1;
| ID | Name | phone_id | phone | ext | notes |
|---|---|---|---|---|---|
| 1 | John | 1 | 687-5309 | 10 | Note1 |
| 1 | John | 2 | 687-5310 | 20 | Note2 |
| 1 | John | 3 | 687-5311 | null | Note3 |
SELECT cust.ID, cust.Name,
group_concat(concat(phone.ID,':',concat_ws(',',phone.phone,ifnull(phone.ext,''),phone.notes)) separator ';') AS phoneList
FROM customer_info AS cust
LEFT JOIN phone_numbers AS phone ON phone.customerID = cust.ID
WHERE cust.ID = 1
GROUP BY cust.ID, cust.Name;
| ID | Name | phoneList |
|---|---|---|
| 1 | John | 2:687-5310,20,Note2;1:687-5309,10,Note1;3:687-5311,,Note3 |
SELECT cust.ID, cust.Name,
group_concat(JSON_OBJECT('id', phone.ID, 'phone', phone.phone, 'ext', phone.ext, 'notes', phone.notes)) AS phoneList
FROM customer_info AS cust
LEFT JOIN phone_numbers AS phone ON phone.customerID = cust.ID
WHERE cust.ID = 1
GROUP BY cust.ID, cust.Name;
| ID | Name | phoneList |
|---|---|---|
| 1 | John | {"id": 3, "phone": "687-5311", "ext": null, "notes": "Note3"},{"id": 2, "phone": "687-5310", "ext": 20, "notes": "Note2"},{"id": 1, "phone": "687-5309", "ext": 10, "notes": "Note1"} |