Posted by theintrepidfox on 10/15/07 07:01

On 15 Oct, 07:29, theintrepidfox <theintrepid...@hotmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Got It working:

LEFT JOIN i2b_lktbl_contact_contactmethod ON
(i2b_lktbl_contact_contactmethod.ContactID = i2b_contact.ContactID)
Instead of
LEFT JOIN i2b_lktbl_contact_contactmethod ON
(i2b_lktbl_contact_contactmethod.ContactMethodID =
i2b_contact.ContactID)

Still, if you have any suggestions for improvement, please let me
know.

Thanks again for your great help and time.

All the best,

Martin

[Back to original message]