I have 2 tables:
person (person_id, first_name, last_name)
phone(phone_id, person_id, number, type)
Each person may have many phone numbers, one for each type
(home,work,cell)
I am trying to create a query that lists every person and their
home-phone number if they have one.
The problem arises when somebody has a work phone but no home phone. I
can't seem to filter out the work phone and still get the person's
name.
I tried this:
SELECT person.*,phone.* FROM person
LEFT JOIN phone
ON person.person_id = phone.person_id AND phone.type='home';
but got an "join expression not supported" on the (phone.type='home')
part.
I tried moving the phone.type into the WHERE clause but that doesn't
work because it removes people that have other phones but no home
phones.
I've done this before but now I can't seem to get it to work
Any help is appreciated.
thanks,
Dean