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,
MartinIs 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:
Quote:
Originally Posted by
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?
Quote:
Originally Posted by
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
selectlcm.ContactID,
cm.ContactMethodTypeID,
min(cm.ContactMethodID) FirstContactMethodID
fromLkTbl_Contact_ContactMethod lcm
joinContactMethod cm
on lcm.ContactMethodID = cm.ContactMethodID
group by lcm.ContactID, cm.ContactMethodTypeID
go
selectc.FirstName,
c.LastName,
cm_home.FirstContactMethodTxt Home,
cm_work.FirstContactMethodTxt Work,
cm_mobile.FirstContactMethodTxt Mobile
fromContact c
left joinv_FirstContactMethodIDs v_fci_home
on c.ContactID = v_fci_home.ContactID
and v_fci_home.ContactMethodTypeID = 'Home'
left joinContactMethod cm_home
on v_fci_home.ContactMethodID = cm_home.ContactMethodID
left joinv_FirstContactMethodIDs v_fci_work
on c.ContactID = v_fci_work.ContactID
and v_fci_work.ContactMethodTypeID = 'Work'
left joinContactMethod cm_work
on v_fci_work.ContactMethodID = cm_work.ContactMethodID
left joinv_FirstContactMethodIDs v_fci_mobile
on c.ContactID = v_fci_mobile.ContactID
and v_fci_mobile.ContactMethodTypeID = 'Mobile'
left joinContactMethod cm_mobile
on v_fci_mobile.ContactMethodID = cm_mobile.ContactMethodID|||On 15 Oct, 05:52, Ed Murphy <emurph...@.socal.rr.comwrote:
Quote:
Originally Posted by
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:
>
Quote:
Originally Posted by
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?
>
Quote:
Originally Posted by
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|||On 15 Oct, 07:29, theintrepidfox <theintrepid...@.hotmail.comwrote:
Quote:
Originally Posted by
On 15 Oct, 05:52, Ed Murphy <emurph...@.socal.rr.comwrote:
>
>
>
>
>
Quote:
Originally Posted by
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).
>
Quote:
Originally Posted by
theintrepidfox wrote:
>
Quote:
Originally Posted by
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
"First" in what sense? Lowest ContactMethodID value among the
candidate rows?
>
Quote:
Originally Posted by
Quote:
Originally Posted by
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)
>
Quote:
Originally Posted by
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
>
Quote:
Originally Posted by
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
No comments:
Post a Comment