I'm a relative newbie to MySQL. I have experimented with selects and joins to filter and combine data from different tables. One thing I'm struggling with is how to output more than one line for one row.
Here is an example I've created to describe what I'm trying to do. A person can have 0 to 3 phone numbers.
ID First Last Bus Phone Home Phone Mobile Phone
40550 Ed Zo 555-145-7424 333-743-1233 123-456-7890
46476 Rui Jun 234-567-8901 345-678-9012
26480 Matt Del 222-333-4444
I would like to create 1 row of output for each phone number the person has.
ID First Last PhoneType Number
40550 Ed Zo B 555-145-7424
40550 Ed Zo H 333-743-1234
40550 Ed Zo M 123-456-7890
46476 Rui Jun B 234-567-8901
46476 Rui Jun H 345-678-9012
26480 Matt Del M 222-333-4444
What SQL statements should I be looking at? Any pointers would be greatly appreciated.
Thank you!
MG
In MySQL, the simplest approach is union all:
select id, first, last, 'B' as phoneType, bus_phone
from t
where bus_phone is not null
union all
select id, first, last, 'h', home_phone
from t
where home_phone is not null
union all
select id, first, last, 'M', mobile_phone
from t
where mobile_phone is not null;