June 23, 2003 at 9:48 am
We are working on a redesign of our call center application and I'm starting on the logical design of the database but want to run this past a few experts.
I have 4 main tables.
Contact - Who the contact is from (Entity), I'm calling about my Provider. I'm the caller - I would be stored in the MemberEntity.
Issue - Who the contact is about - (Entity) , The Issue is about my Provider.
EntityType - Member or Provider
Entity - Will store all the Entities regardless of type.
Member - specific information about an Member entity.
Provider - specific information about an Provider entity
Select c.ContactID,
I.IssueID,
'IssueAbout' =
Case
When et.EntityTypeID = 1 THEN 'MEMBER'
When et.EntityTypeID = 2 THEN 'PROVIDER'
When et.EntityTypeID = 3 THEN 'NON-MEMBER'
End
-- I will also need the member name or provider name
-- If provider I need the TaxID / If member I need the MemberID
-- Can I do a join in the case?
--Do I need to use If - Provider do this / If member do this.
from Contact as c
Inner Join Issue as I on I.ContactID = C.ContactID
Inner Join Entity on E.EntityID = I.AboutEntityID
Inner Join EntityType as et on et.EntityTypeID = E.EntityTypeID
-- If the call is about a member or a provider I need to join either to the Provider table or the Member table.
-- What is the best way to do it?
Thanks for any help you can give.
June 23, 2003 at 11:02 am
If I understand correctly then something like this could work:
In the SELECT list you need to display TaxID or MemberID but not both. Try this:
'ID' =
CASE
WHEN et.EntityTypeID = 1 THEN MemberID
WHEN et.EntityTypeID = 2 THEN TaxID
ELSE NULL
END
The join is different. There are a few ways to do this. One is to have two seperate queries, one with the Provider table and the other with the Member table. Then simply determine before executing a query which one should run. Its likely that this one will be the best option for performance.
Another option is to join both tables as LEFT JOINS to the from clause you have. Of course this usually means you will be pulling data you don't need for any reason and that could slow down your query. I would give it a try and see if the performance is acceptable.
Then you can simply use CASE to determine what columns from each table you want to display.
I hope some of this helps.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
June 23, 2003 at 12:00 pm
Robert,
I think your right my best option is to write two queries
One to join to provider and one to join for member depending on the EntityType.
If EntityType = 1
Begin
--Join to Member Table
End
If EntityType = 2
Begin
-- Join to Provider Table
End
Thanks,
Mike
June 24, 2003 at 2:38 am
You can use :
Select c.ContactID,
I.IssueID,
'IssueAbout' = 'MEMBER',
MemberID,
MemberName,
from Contact as c
Inner Join Issue as I on I.ContactID = C.ContactID
Inner Join Entity on E.EntityID = I.AboutEntityID
Inner Join EntityType as et on et.EntityTypeID = E.EntityTypeID
Inner Join Member as M on
et.EntityID = M.MemberID
Where et.EntityTypeID = 1
UNION
Select c.ContactID,
I.IssueID,
'IssueAbout' = 'PROVIDER',
TaxID,
TaxName,
from Contact as c
Inner Join Issue as I on I.ContactID = C.ContactID
Inner Join Entity on E.EntityID = I.AboutEntityID
Inner Join EntityType as et on et.EntityTypeID = E.EntityTypeID
Inner Join Provider as P on
et.EntityID = P.MemberID
Where et.EntityTypeID = 2
I hope it be usefull in your case
Trang
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply