I have 2 tables which I need to join, but sometimes there are
1) direct joins e.g.
t1.FieldA = t2.FieldA
null then it must join as well then
t2.FieldA is null
t1.FieldA = isnull(t2.FieldA,t1.fieldA)
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
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
- Does all 5 match?
A, B, C, Dmatch? then 4
A, B, D, Ematch? then 4
A, B, C, Ematch? then 4
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