Posted by theintrepidfox on 10/14/07 18:42

Dear Group

I'd be grateful if you can provide me with a hint for the following:

Fields Table Contact
ContactID
Firstname
Lastname

Fields Table ContactMethod
ContactMethodID
ContactMethodTxt
ContactMethodTypeID

Linked by Tables:

Fields Table LkTbl_Contact_ContactMethod
LkTblID
ContactID
ContactMethodTypeID

Fields Table ContactMethodType
ContactMethodTypeID
ContactMethod

The purpose of this construct is that I keep all contactmethods such
as Email, Mobile, Home Work, Web in table ContactMethods. Whether it's
e.g. an Email or Mobile is identified through the ContactMethodType.
That enables me that a Contact can have 3 Mobile Numbers, 2 Email
addresses.

For the sake of simplicity, lets say I want to return the Contact Name
and Home, Work and Mobile numbers. Not all of them but the first
matching record of each from the ContactMethod table.

I must note that the contents of table ContactMethodType for column
Contactmethod looks like this:
Email
Mobile
Home
Work
Web

I do:

Select Firstname, Lastname, Home, Work, Mobile FROM Contact
JOIN LkTbl_Contact_ContactMethod ON
(LkTbl_Contact_ContactMethod.ContactID = Contact.ContactID)
JOIN ContactMethod ON (ContactMethod.ContactMethodID =
LkTbl_Contact_ContactMethodID)
JOIN ContactMethodType ON (ContactMethodType.ContactMethodTypeID =
ContactMethod.ContactMethodTypeID)

The question is, how do I map the result from table ContactmethodType
to my fields Home, Work and Mobile in the resultset? The Type is
identified in ContactMethodType but I can't figure out how to do this
best.

Your help and suggestions or any other better solution is very
appreciated.

Thanks in advance for your help and efforts,

Martin

[Back to original message]