Confusing MySQL query

Question

I have two tables, staff and phones.

Staff has only one field, staff_id. Phones has three fields: staff_id, phone_type and number.

I want to display the staff_id, cell phone number and home phone number of all staff members. But I can't figure out how to have cell phone number and home phone number as separate columns in the result. Here's what I've been trying so far, which puts both types of numbers in the same column.

SELECT staff.staff_id, phones.number
FROM staff
LEFT JOIN phones ON ( staff.staff_id = phones.staff_id && ( phones.field_type = 'Cell' || phones.field_type = 'Home' ) )

Answer 1

You can't do it this way, as you can't assign values to columns.
You would have to do 2 joins:

SELECT staff.staff_id, cells.number, home.number FROM staff 
  JOIN phones AS cells ON (...)
  JOIN phones AS home ON (...)
  WHERE cells.field_type='Cell' AND home.field_type='Home';

It would work, but you won't have a staff members home and cell phone number in one column.

Answer 2

You need to join the phones table twice.

SELECT staff.staff_id, cellPhones.number, homePhones.number,
FROM staff
LEFT JOIN phones cellPhones ON ( staff.staff_id = phones.staff_id && phones.field_type = 'Cell' )
LEFT JOIN phones homePhones ON ( staff.staff_id = phones.staff_id && phones.field_type = 'Home' )

Answer 3

This is like the problem I had with my addressbook.

You can find the answer here: SQL Database problems with addressbook table design

Answer 4

You need to use a pivot query, something like the untested code below:

select staff.staff_id,
       MAX(IF(phones.field_type='Cell', phones.number, null)) as Cell,
       MAX(IF(phones.field_type='Home', phones.number, null)) as Home
from   staff,
       phones
where  phones.staff_id = staff.staff_id
group by staff.staff_id

Note - joining against the phones table multiple times would also work, but the solution above should perform better and can easily be extended for additional phones.field_types.

See also http://dev.mysql.com/doc/refman/5.1/en/select.html (search for "pivot").