Posted by Ed Murphy on 10/15/07 04:52

Is Contact <-> ContactMethod a many-to-many relationship, i.e. can a
single ContactMethod be associated with multiple Contacts? If not,
then I agree with Erland that ContactMethod should be merged with
LkTbl_Contact_ContactMethod for simplicity (unless you have a pile of
code already built on top of the existing design, in which case you
should at least create a view that combines their data).

theintrepidfox wrote:

> 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.

"First" in what sense? Lowest ContactMethodID value among the
candidate rows?

> 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)

create view v_FirstContactMethodIDs as
select lcm.ContactID,
cm.ContactMethodTypeID,
min(cm.ContactMethodID) FirstContactMethodID
from LkTbl_Contact_ContactMethod lcm
join ContactMethod cm
on lcm.ContactMethodID = cm.ContactMethodID
group by lcm.ContactID, cm.ContactMethodTypeID
go

select c.FirstName,
c.LastName,
cm_home.FirstContactMethodTxt Home,
cm_work.FirstContactMethodTxt Work,
cm_mobile.FirstContactMethodTxt Mobile
from Contact c
left join v_FirstContactMethodIDs v_fci_home
on c.ContactID = v_fci_home.ContactID
and v_fci_home.ContactMethodTypeID = 'Home'
left join ContactMethod cm_home
on v_fci_home.ContactMethodID = cm_home.ContactMethodID
left join v_FirstContactMethodIDs v_fci_work
on c.ContactID = v_fci_work.ContactID
and v_fci_work.ContactMethodTypeID = 'Work'
left join ContactMethod cm_work
on v_fci_work.ContactMethodID = cm_work.ContactMethodID
left join v_FirstContactMethodIDs v_fci_mobile
on c.ContactID = v_fci_mobile.ContactID
and v_fci_mobile.ContactMethodTypeID = 'Mobile'
left join ContactMethod cm_mobile
on v_fci_mobile.ContactMethodID = cm_mobile.ContactMethodID

[Back to original message]