Strong and Weak Join Matches

Database

Strong and Weak Join Matches

I have two tables that I join on two fields. Policy and Claim. I want results from three queries 

1. Policy.A = Policy.B and Claim.A = Claim.B

2. Policy.A = Policy.B and Claim.A <> Claim.B

3. Policy.A <> Policy.B and Claim.A = Claim.B

But I want to exclude rows matched in query 1 from queries 2 and 3. Here are two example tables and the results I want:

Table A

Policy | Claim

123 | abc

123 | abd

124 | abe

125 | abf

126 | null

null | abi

Table B

Policy | Claim

123 | abc

123 | abd

126 | abe

125 | abg

126 | abh

127 | abi

Results I want:

123 | abc | 123 | abc (matches on both policy and claim)

123 | abd | 123 | abd (matches on both policy and claim)

124 | abe | 126 | abe (matches on claim)

125 | abf | 125 | abg (matches on policy)

126 | null | 126 | abh (matches on policy)

null | abi | 127 | abi (matches on claim)

Example results I don't want:

123 | abc | 123 | abd (matches on policy but not claim)

123 | abd | 123 | abc (matches on policy but not claim)

I don't want the two results above because I already have a "strong" match on both policy and claim. Essentially I want the rows that match perfectly to be excluded from the weaker matches on either policy OR claim. How could I go about creating this query without a bunch of messy subqueries?

4 REPLIES
Enthusiast

Re: Strong and Weak Join Matches

where (Policy.A = Policy.B and Claim.A <> Claim.B ) or  ( Policy.A <> Policy.B and Claim.A = Claim.B )  if  you use "()" then its will give you the result with excluding the 1 join condition

Senior Apprentice

Re: Strong and Weak Join Matches

You have to be more precise on your rules.

Why is there only one row for policy 126? 

I would assume two row because there are two rows for 126 in table B.

Why is 126 | null | 126 | abe no valid result? 

Dieter

Re: Strong and Weak Join Matches

126 | null | 126 | abe would be valid. My mistake.

Senior Apprentice

Re: Strong and Weak Join Matches

Try this:

SELECT a.*,
COALESCE(B1.Policy, B2.Policy, B3.Policy) AS Policy2,
COALESCE(B1.Claim, B2.Claim, B3.Claim) AS Claim2
FROM tableA AS A
LEFT JOIN tableB AS B1
ON A.Policy = B1.Policy
AND A.Claim = B1.Claim
LEFT JOIN tableB AS B2
ON A.Policy = B2.Policy
AND B1.Claim IS NULL -- only those rows with no match in join #1
AND B1.Policy IS NULL
LEFT JOIN tableB AS B3
ON A.Claim = B3.Claim
AND B2.Claim IS NULL -- only those rows with no match in join #1 and #2
AND B2.Policy IS NULL

Dieter