Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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