How to find the row with the maximum matching columns?

Question

If there are three tables, TableItem, TableAbcd and TablePqrs, as below

TableItem
ID  item
1   item1

TableAbcd
ID  Item    ColA    ColB    ColC    ColD
1   item1   A1      B1      C1      D1


TablePqrs
ID  item    ColA    ColB    ColC    ColD    ColValue
1   item1   A1      B1      null    null    10000
2   item1   A1      B1      C1      D1      100

Here, for a given Item, There has to be just one record in the output which has the maximum columns matching in TableAbcd and TablePqrs. Since row 1 of TableAbcd has maximum matching columns with TablePqrs row 2.

My output for join with above three tables should be,

item    ColA    ColB    ColC    ColD    ColValue
item1   A1      B1      C1      D1      100

Code tried so far,

Select  item,   ColA,   ColB,   ColC,   ColD,   ColValue
     FROM TableItem a 
         LEFT OUTER JOIN TableAbcd b
         ON a.item = b.item      
         LEFT OUTER JOIN TablePqrs c
         ON (b.ColA = c.ColA AND b.ColB = c.ColB AND b.ColC = c.ColC AND b.ColD = c.ColD)
         OR (b.ColA = c.ColA AND b.ColB = c.ColB AND b.ColC = c.ColC)
         OR (b.ColA = c.ColA AND b.ColB = c.ColB)

if fetch's me two records, i know there may be design issues, but we are getting data from third party legacy system, which has table structure as per its needs and sending this to another interface.

Please suggest.

Answer 1

I tried the below thing and it worked, the coalesce helps me prioritise which value to pick depending upon the order i mention in it.

Select  item,   ColA,   ColB,   ColC,   ColD,   ColValue
     FROM TableItem a 
    LEFT OUTER JOIN (
         SELECT item,
            COALESCE(c1.ColValue,c2.ColValue,c3.ColValue) ColValue
        FROM abc b
        LEFT OUTER JOIN pqr c1 
            ON b.ColA = c1.ColA AND b.ColB = c1.ColB AND b.ColC = c1.ColC AND b.ColD = c1.ColD
        LEFT OUTER JOIN pqr c2 
            ON b.ColA = c2.ColA AND b.ColB = c2.ColB AND b.ColC = c2.ColC
        LEFT OUTER JOIN pqr c3 
            ON b.ColA = c3.ColA AND b.ColB = c3.ColB
        GROUP BY item
     ) as Fact 
     ON Fact.item = a.item