Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Variable SQL join

Author  Topic  tcwassel Starting Member 1 Post Posted - 2009-08-08 : 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!