0

I have 2 tables which I need to join, but sometimes there are

1) direct joins e.g. t1.FieldA = t2.FieldA

OR

2) when t2.FieldA is null then it must join as well then t2.FieldA is null t1.FieldA = isnull(t2.FieldA,t1.fieldA)

BUT

I have to keep track of how many were direct joins, how many were isnull joins, since there are precedent levels which need to be calculated, e.g. even though the items joined the one where there are 5 direct joins are higher than when it joined on 5 isnull values

Now it is a hell of a mission to have the following in the Select statement, since there can be more than 5 fields to compare.

e.g. I need to test

  1. Does all 5 match?
  2. Does A, B, C, D match? then 4
  3. Does A, B, D, E match? then 4
  4. Does A, B, C, E match? then 4

etc.

This is way to sloppy, I am looking for help on method to count how many were directly joined please

case when t1.A = t2.A and t1.B = t2.B and t1.C = t2.C and t1.D = t2.D  and t1.E = t2.E  then  5 
     when t1.A = t2.A and t1.B = t2.B and t1.C = t2.C and t1.D = t2.D                 then  4
     when t1.B = t2.B and t1.C = t2.C and t1.D = t2.D and t1.E = t2.E                 then  4
     when t1.A = t2.A and t1.E = t2.E                 then 2

Here is a SQL Fiddle

1

How about :

CASE WHEN t2.A IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN t2.B IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN t2.C IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN t2.D IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN t2.E IS NOT NULL THEN 1 ELSE 0 END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.