Skip to Content
avatar image
Former Member

Case Else pulling from 2 different tables

I am trying to retrieve a name based on a certain type of claim. Would seem easy enough, but it's not working for me. I am working with 2 main tables, claim and exposure, and a ContactName table. Each claim can have multiple exposures, but each exposure can have only one claim. Both tables contain a nameID field - claim.nameID and exposure.nameID. The nameID correlates to ContactName table.

Contact Name
ID        Name
111      Jim
222      Bob
333      John
444      Sam
555      Walt

Normally, each claim can have multiple exposures and the exposures on a claim can have the same name or different names. The name ids are found on the exposure.The exposure.nameID joins ContactName.ID

Claim   claim.nameID  ExposureNo   exposure.nameID        Name
A       null              1         111                   Jim
A       null              2         222                   Bob
A       null              3         333                   John
B       null              1         444                   Sam
B       null              2         444                   Sam

Workers comp claims are different. Each workers comp claim can have multiple exposures, but all the exposures have the same name. Since the exposures on a claim have the same name, the name ids are found, not on the exposure table, but the claim table. Go figure…The claim.nameID joins ContactName.ID AS ClaimContactName.

Claim   claim.nameID  ExposureNo   exposure.nameID        Name
C       111              1          null                  Jim
C       111              2          null                  Bob
C       111              3          null                  John
D       555              1          null                  Sam
D       555              2          null                  Sam

I can get the names for non workers comp claims and can get the names for workers comp claims. However, when I put the 2 queries together, I don’t get any results.

Select
CASE WHEN @Select(Claim\Policy Type) = 'Workers'' Compensation' THEN
CASE WHEN ClaimContact.Name IS NULL AND ClaimContact.Name.FirstName IS NULL THEN ClaimContact.LastName
WHEN ClaimContact.Name IS NOT NULL THEN ClaimContact.Name
ELSE ClaimContact.FirstName + Space(1) + ClaimContact.LastName END
ELSE
CASE WHEN Contact.Name IS NULL AND Contact.FirstName IS NULL THEN
Contact.LastName 
WHEN Contact.Name IS NOT NULL THEN Contact.Name
ELSE Contact.FirstName + Space(1) + Contact.LastName END
END

Haven’t done much of this, but could really use the help to figure this out. Thank you in advance.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers