By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 896 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

JOINing detail tables into the main table

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

SELECT person.*, phone.number
FROM person LEFT JOIN ON person.person_id = phone.person_id
WHERE (((phone.type)='home'));
--

Tony D'Ambra
Web Site: aadconsulting.com
Web Blog: accessextra.net

<re******@bigfoot.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
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

Nov 13 '05 #2

P: n/a
Tony - thanks for the input but that doesn't work either. That doesn't
show anybody unless they have a home phone. I want the people to show
up even if they don't have a home phone.

I did get it to work by placing () around my ON clause.

ON ((person.person_id = phone.person_id) AND (phone.type='home'))

The reason it quit working was that Access Design View decided to
remove the outter parenthesis without telling me. I wish I could
figure out how to turn that "feature" off.

Well as long as I don't edit the SQL statement everything seems to stay
as is.

-Dean

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.