Skip to content
Advertisement

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

  • Table 2; columns

  • Table 3; columns

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:

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

Advertisement

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:

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement