# Joining 2 tables- in Select statement show how many fields matched directly (A1 = B1), vs matched on isnull(A1,B1) = B1

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

``````CASE WHEN t2.A IS NOT NULL THEN 1 ELSE 0 END