mysql - INNER or LEFT Joining Multiple Table Records Into A Single Row

Question

Phone Table

+----------------+-------------+
| Field          | Type        |
+----------------+-------------+
| f_id           | int(15)     |
| f_client_id    | int(11)     |
| f_phone_type   | varchar(50) |
| f_phone_number | varchar(13) |
+----------------+-------------+

Clients Table

+-----------------------------+--------------+------+-----+
| Field                       | Type         | Null | Key |
+-----------------------------+--------------+------+-----+
| f_id                        | int(15)      | NO   | PRI |
| f_first_name                | varchar(13)  | YES  | MUL |
| f_mi                        | char(1)      | YES  |     |
| f_last_name                 | varchar(20)  | NO   | MUL |
+-----------------------------+--------------+------+-----+

Assumptions:

1. Each record in 'Phone Table' belongs to one record in 'Clients Table'.
2. Each record in 'Clients Table' can have 0 or more records in 'Phone Table'.
3. Simple Translation: A client can have 0 or more phone numbers

With a standard LEFT or INNER join, I get something like this:

+------------+------------+--------------+
| name       | Phone Type | Phone Number |
+------------+------------+--------------+
| John Smith | Home       | 712-555-6987 |
| John Smith | Work       | 712-555-1236 |
+------------+------------+--------------+

I need a query that will give me the work and home numbers that belong to a given client:

+------------+----------------+--------------+
| Name       | Work Number    | Home Number  |  
+------------+----------------+--------------+
| John Smith | 712-555-1236   | 712-555-6987 |
+------------+----------------+--------------+

Is it possible to do a LEFT or INNER join and then merge those results into a single row? I've seen similiar questions on this, but the examples given were much more complex than what I'm after:

Similiar Questions

• MySQL Need help constructing query: join multiple tables into single row
• SQL left join with multiple rows into one row

Thanks

Answer 1

Though you can join several numbers (in any) into a single field:

SELECT
  CONCAT(f_first_name, ' ', f_last_name) as Client_Name,
  GROUP_CONCAT(IF(phone_type='work',f_phone_number, NULL)) as Work_Numbers,
  GROUP_CONCAT(IF(phone_type='home',f_phone_number, NULL)) as Home_Numbers
FROM clients
JOIN phone
  USING (f_id)
WHERE phone_type IN ('home', 'work')
GROUP BY f_id;