Posted - 08/08/2009 : 20:48:04
Hello, I hope someone may have some suggestions for a SELECT statement I am trying to create.
I have a primary table like:
Field1 Field2 Field3
1_____AAA____BBB
2_____BBB____AAA
1_____CCC____DDD
5_____DDD____KKK
Secondary table like:
Field1 Field2 Field3 Field4
1__________________X
2____BBB____AAA____Y
1____CCC___________Z
___________________J
Desired Result:
Field1 Field2 Field3 Field4
1......AAA....BBB....X
2......BBB....AAA....Y
1......CCC....DDD....Z
5......DDD....KKK....J
The idea here is I want to join to the secondary table based on the maximum number of matching fields. And if there are no matching fields I want to join to the record where field1, field2, and field3 are blank (or null) and field 4 = 'J'. Notice that in the desired result, record 1 selects 'X' because the only match is on Field1 while record 3 selects 'Z' because there is a match on Field1 and Field2.
Any help or suggestions is much appreciated. Thanks!