How to compare 2 tables based on a column in MS SQL?

Cenk 666 Reputation points
2023-01-19T12:02:41.0533333+00:00

Hi guys,

There is a reconciliation between the two systems at certain periods, but this reconciliation is compared with the result obtained by querying the database and excel file. To automate this manual process, I insert the incoming excel into a table in the database with SQL Bulk Copy. I will then compare these two tables.

The GameConfirmResponses table has all the transactions. (It may make more sense to filter by the date range to be reconciled.) RazerReconciliation is the table that I populated from excel. There might be transactions that the GameConfirmResponses table has and the RazerReconciliation table does not and vice versa so somehow I need to check that scenario too in the SQL query.

I hope I was able to explain my problem.

Select ReferenceNo
From RazerReconciliation A
Where NOT EXISTS
( select referenceId from GameConfirmResponses B
Where A.ReferenceNo = B.referenceId
);
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,539 questions
No comments
{count} votes

Accepted answer
  1. Naomi 6,266 Reputation points
    2023-01-19T14:42:56.1466667+00:00

    How about?

    Select ReferenceNo, 'In Razer' as Type From RazerReconciliation A 
    
    Where NOT EXISTS 
    ( select referenceId from GameConfirmResponses B Where A.ReferenceNo = B.referenceId )
    
    UNION ALL
    
    select referenceId, 'In Game' as Type from GameConfirmResponses a
    
     where not exists (select 1 from 
    
    RazerReconciliation b WHERE a.ReferenceID = b.ReferenceNo)
    ;
    

0 additional answers

Sort by: Most helpful