Reputation: 7725
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' ) )
Upvotes: 1
Views: 149
Reputation: 5478
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").
Upvotes: 2
Reputation: 9373
This is like the problem I had with my addressbook.
You can find the answer here: SQL Database problems with addressbook table design
Upvotes: 1
Reputation: 8084
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' )
Upvotes: 1
Reputation: 10281
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.
Upvotes: 1