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

seeking asistance with query

P: n/a
I am trying to do a query to produce a list of employee and their
telephone numbers (IF ANY) which joins on 3 tables. The complication
is the 3rd table, the table containing telephone numbers. That table
has both cellular and regular numbers. The type of phone number is
indicated by a column -phoneType) containing the value 0 for regular
numbers and 1 for cells.

I am getting errors about ambigous outer joins, or only getting results
where both types of numbers exist. I am pretty lost! Any help much
appreciated.

I have the following 3 tables in a database

Contact table, employee table, PhoneNumberstable. The employee and
PhoneNumberstable have a column called contact ID which is a foreign
key to the contact table.

For each employee, I need to select the last name from the contact
table and then regular tel number (if it exists) and cell phone number
(if it exists). I want all the info in a single row, not multiple rows.
I need something like this...
LastName Phone (0) CellPhone (1)
ABC (123)555-1212 NULL
DEF NULL (000)555-1212
GHI (123)456-7890 (000)123-4567
JKL NULL NULL

The following query gives 1 row for each contactID/phone number match.
Again I want 1 row for each contact.

SELECT Contacts.FirstName, Contacts.LastName, PhoneNumbers.phoneNum
FROM (Contacts INNER JOIN employee ON Contacts.ContactID =
employee.ContactID) LEFT JOIN PhoneNumbers ON Contacts.ContactID =
PhoneNumbers.ContactID;

Aug 11 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
You're not going to get the spreadsheet type result that you want unless you
create a report (based on your query) and set a group for the contactID.
-Ed

"Welie" <we******@gmail.comwrote in message
news:11*********************@i42g2000cwa.googlegro ups.com...
>I am trying to do a query to produce a list of employee and their
telephone numbers (IF ANY) which joins on 3 tables. The complication
is the 3rd table, the table containing telephone numbers. That table
has both cellular and regular numbers. The type of phone number is
indicated by a column -phoneType) containing the value 0 for regular
numbers and 1 for cells.

I am getting errors about ambigous outer joins, or only getting results
where both types of numbers exist. I am pretty lost! Any help much
appreciated.

I have the following 3 tables in a database

Contact table, employee table, PhoneNumberstable. The employee and
PhoneNumberstable have a column called contact ID which is a foreign
key to the contact table.

For each employee, I need to select the last name from the contact
table and then regular tel number (if it exists) and cell phone number
(if it exists). I want all the info in a single row, not multiple rows.
I need something like this...
LastName Phone (0) CellPhone (1)
ABC (123)555-1212 NULL
DEF NULL (000)555-1212
GHI (123)456-7890 (000)123-4567
JKL NULL NULL

The following query gives 1 row for each contactID/phone number match.
Again I want 1 row for each contact.

SELECT Contacts.FirstName, Contacts.LastName, PhoneNumbers.phoneNum
FROM (Contacts INNER JOIN employee ON Contacts.ContactID =
employee.ContactID) LEFT JOIN PhoneNumbers ON Contacts.ContactID =
PhoneNumbers.ContactID;

Aug 11 '06 #2

P: n/a

Ed Robichaud wrote:
You're not going to get the spreadsheet type result that you want unless you
create a report (based on your query) and set a group for the contactID.
-Ed
Thanks for the reply Ed. And take this as a newbie question, since I
know little about Reports, but won't that still put 2 rows on the
report for each contactID? The result set of the query is still the
same. How do I tell the report not to publich the employees name twice?

Thanks!

Aug 11 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.