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

• http://stackoverflow.com/questions/2485210/mysql-need-help-constructing-query-join-multiple-tables-into-single-row
• http://stackoverflow.com/questions/2561280/sql-left-join-with-multiple-rows-into-one-row

Thanks

Answer 1

SELECT CONCAT(c.f_first_name, ' ', c.f_last_name) as Client_Name, 
       wp.f_phone_number as Work_Number,
       hp.f_phone_number as Home_Number

  FROM clients c
       LEFT OUTER JOIN phone hp
       ON hp.f_client_id = c.f_id
    AND
       hp.phone_type = 'home'
       LEFT OUTER JOIN phone wp
       ON wp.f_client_id = c.f_id
    AND
       wp.phone_type = 'work'

With LEFT OUTER JOINs you will still get rows for clients with missing numbers. If you don't want to see those, change to INNER JOINs.

Edit: As Nick kindly reminds me, this will return multiple rows for clients with multiple phone numbers. Once you have the data you need, you're then faced with presentation issue. You can handle that in the application layer, or make a sacrifice to the SQL gods and look into MySQL's GROUP_CONCAT() function.

Answer 2

Are there limits on how many Work or Home numbers a particular Client record can have? If it can be many, then no, there is no way to make a single row. If there can be at most 1 of each, then you can just join on the phone numbers table twice.

Answer 3

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;