Compare 3 SQL Server tables and once matched based on some attribute put the result on one table when not matched put the result in another

I have the simple requirement, there are the 3 SQL Server tables like this:

• Table 1; columns

    AID  Name  DOB  Gender  PostCode
  

• Table 2; columns

    BID  Name  DOB  Gender  PostCode
  

• Table 3; columns

    CID  Name  DOB  Gender  PostCode
  

I wish to join (inner joins) based on Name, DOB, Gender & PostCode across all the tables, if the record matches I would like to put the result in one table and the remaining records I wish to put in another table for further comparison/processing.

The first part is simple with this query:

SELECT AID, BID, CID, A.Name, A.DOB, A.Gender, A.PostCode
FROM TAB_A
JOIN TAB_B B ON A.Name = B.Name 
             AND A.DOB = B.DOB 
             AND A.Gender = B.Gender 
             AND A.PostCode = B.PostCode
JOIN TAB_C C ON A.Name = C.Name 
             AND A.DOB = C.DOB 
             AND A.Gender = C.Gender 
             AND A.PostCode = C.PostCode

But 2nd part on the remaining records which doesn’t match need to put them in a separate table.

For example if there are total 50 records in all the 3 tables (A = 20, B=20 & C=10) and the output where all the records are matching based on the above query are 5, I wish to store 95 records in the separate table.

Your help/answer will be appreciated.

Thanks

Answer

You can use union all to combine the tables. Then use a window function to count the matches by the columns you care about.

Return the rows where the match is less than 3:

select id, Name, DOB, Gender, PostCode
from (select id, Name, DOB, Gender, PostCode,
count(*) over (partition by Name, DOB, Gender, PostCode) as cnt
      from ((select AID as id, Name, DOB, Gender, PostCode
             from a
            ) union all
            (select BID, Name, DOB, Gender, PostCode
             from b
            ) union all
            (select CID, Name, DOB, Gender, PostCode
             from c
            )
           ) abc
      ) abc
where cnt < 3;