Match and merge when comparing data in 4 tables

Question

I have a requirement to create SQL Server user defined function/SP (either normal or table valued function) which has the below requirements:

1. The data across 4 tables (Table_A, Table_B, Table_C, Table_D) should be matched based on fix attributes (Name in our below example)
2. If the data matches in all the 4 tables it gets the highest score & uniqueID is created. For example Match Type = ABCD
3. If the data matches in other combinations of 3 tables than it gets some score and different UniqueID. For example Match Type = ABC, ABD, BCD, CDA
4. If the data matches in other combinations of 2 tables than it gets some score and different UniqueID. For example Match Type = AB, AC, AD, BC, BD, CD
5. Records that doesn't match will get 0 score with separate UniqueID will be stored in the same table.

Table_A

AID | Name | ZipCode

Table_B

BID | Name | ZipCode

Table_C

CID | Name | ZipCode

Table_D

DID | Name | ZipCode

It matches on Name and ZipCode attributes

Final or match and merge table:

UID | AID | BID | CID | DID | Match_Score

Please suggest how can we create a function/stored procedure for the above requirements. If we can make it robust and expandable would be better i.e. If one more tables get added the logic should work with minimal code changes.

Really appreciate your help in this case.

I can think of the below approach but not sure if that can be coded -

ABCD (Output of the table where all the record matches)
UNION ALL
ABC (This will run only on the records that are not par of the ABCD result)
UNION ALL
ACD (This will run only on those records which are not a part of the above 2 results)
UNION ALL
and on and on

Answer 1

1. Break it down into smaller sections using a temp table for each section and then do your final merge
2. In your final rank them based on the how many matches.

The typical merge syntax is like so.. remember merge can only have one target table, but multiple sources

MERGE TOP (value) <target_table> 
USING <table_source>   
ON <merge_search_condition>  
    [ WHEN MATCHED [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] 
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]  
        THEN <merge_not_matched> ]  
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] 
    [ <output_clause> ]  
    [ OPTION ( <query_hint> ) ]      
;

---

As a simple easy sample, find names and join them... you can start with this... and refine it to your needs by using something like a DECLARE @MyNumberOfMatchesVariable INT and update it as you get matches

select *
from (
        select someUniqueValueToMatchfrom table1
        union
        select someUniqueValueToMatchfrom table2
        union
        -- ...
        select someUniqueValueToMatchfrom table..
    ) distinct_usernames
    left join table1 on table1.someUniqueValueToMatch= distinct_usernames.Username
    left join table2 on table2.someUniqueValueToMatch= distinct_usernames.Username
    -- ...
    left join table... on table....someUniqueValueToMatch= distinct_usernames.Username

---

Answer 2

Joining the 4 tables using FULL JOINs will give you all the various combinations:

SELECT AID,BID,CID,DID,
       CASE WHEN AID IS NULL THEN 0 ELSE 1 END
     + CASE WHEN BID IS NULL THEN 0 ELSE 1 END
     + CASE WHEN CID IS NULL THEN 0 ELSE 1 END
     + CASE WHEN DID IS NULL THEN 0 ELSE 1 END/*,
       CASE WHEN AID IS NULL THEN '' ELSE 'A' END
     + CASE WHEN BID IS NULL THEN '' ELSE 'B' END
     + CASE WHEN CID IS NULL THEN '' ELSE 'C' END
     + CASE WHEN DID IS NULL THEN '' ELSE 'D' END*/
FROM Table_A a
FULL JOIN Table_B b ON a.Name=b.Name AND a.ZipCode=b.ZipCode
FULL JOIN Table_C c ON a.Name=c.Name AND a.ZipCode=c.ZipCode OR b.Name=c.Name AND b.ZipCode=c.ZipCode
FULL JOIN Table_D d ON a.Name=d.Name AND a.ZipCode=d.ZipCode OR b.Name=d.Name AND b.ZipCode=d.ZipCode OR c.Name=d.Name AND c.ZipCode=d.ZipCode