Posted by theintrepidfox on 10/15/07 06:29

On 15 Oct, 05:52, Ed Murphy <emurph...@socal.rr.com> wrote:
> 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

Hi Ed

Thanks for your message.

'Is Contact <-> ContactMethod a many-to-many relationship, i.e. can a
single ContactMethod be associated with multiple Contacts?'

Yes, Contact A and Contact B both might share the same BusinessPhone
(ContactMethodType) in which case there's a single row in table
ContactMethod that refers to both.

I got the script almost working based on Erlands sample. The only
issue is that it returns me value Phone of row 1 ('777 123') in table
Contactmethod instead row 6 ('435 675') which would be the correct
one. Here's what I got, it's probably totally wrong. Please excuse my
SQL ignorance.

SELECT i2b_jajah.JajahID, UseJajah, JajahUsername,
MIN(ISNULL(NULLIF(Firstname,'') + CHAR(32),'') +
ISNULL(NULLIF(Middlename,'') + CHAR(32),'') + ISNULL(Lastname,'')) AS
RealUsername,
Phone = Min(CASE i2b_systbl_contactmethodtype.ContactMethodTypeID
WHEN 1 THEN i2b_contactmethod.ContactMethodText END)
FROM i2b_jajah
LEFT JOIN i2b_lktbl_contact_jajah ON (i2b_lktbl_contact_jajah.JajahID
= i2b_jajah.JajahID)
JOIN i2b_contact ON (i2b_contact.ContactID =
i2b_lktbl_contact_jajah.ContactID)
LEFT JOIN i2b_lktbl_contact_contactmethod ON
(i2b_lktbl_contact_contactmethod.ContactMethodID =
i2b_contact.ContactID)
JOIN i2b_contactmethod ON (i2b_contactmethod.ContactMethodID =
i2b_lktbl_contact_contactmethod.ContactMethodID)
JOIN i2b_systbl_contactmethodtype ON
(i2b_systbl_contactmethodtype.ContactMethodTypeID =
i2b_contactmethod.ContactMethodTypeID)
GROUP BY i2b_jajah.JajahID, UseJajah, JajahUsername

Result:
JajahID UseJajah JajahUsername
RealUsername Phone
---------- ------------
---------------------
--------------------- ---------
1 0 JSmith007
John Smith 777 123


Thank you for your help and efforts,

Martin

[Back to original message]