CODEWITHSUNDEEP
sqlsql-servercomparison
Hotlansy Httlandy
Hotlansy Httlandy

Reputation: 349

SQL Server Comparing two tables

I created a temp table:

 CREATE table #Temp
(
    ACCOUNT varchar(20),
    SERV_ACCT varchar(20),
    INV_DATE datetime,
    CURR_Date datetime
)

and inserted values in it.

I have another table called: AccountTable which has the four columns: ACCOUNT, SERV_ACCT, INV_DATE, CURR_Date

After I insert values into the #Temp can I do this to find all the matching results:

SELECT * FROM #Temp
JOIN AccountTable 
ON #Temp.ACCOUNT = AccountTable.ACCOUNT
AND  #Temp.SERV_ACCT = AccountTable.SERV_ACCT
AND  #Temp.INV_DATE = AccountTable.INV_DATE
AND  #Temp.CURR_Date = AccountTable.CURR_Date

Would this return only the matching results? Is there another way I can compare?

Upvotes: 1

Views: 4416

Answers (2)

Fabian Bigler
Fabian Bigler

Reputation: 10895

Would this return only the matching results?

Yes. This returns the matching results.

So as an example:

table AccountTable:

brian -- bacon -- 02/08/2013 -- 02-08-2013
chicken -- spam -- 01/08/2013 -- 01-08-2013

table #Temp:

brian -- bacon -- 02/08/2013 -- 02-08-2013

Your query would return the brian-row.

But consider if your dates are different. Then a match only based on ACCOUNT and SRV_ACC could be more appropriate, because you still want to show these rows. So it really depends on the context what makes sense, which is up to you.

Is there another way I can compare?

It depends on what you want to achieve. As Goat CO already pointed out, you can also show the non-matching rows. You could also compare which data are newer, or which table consists of more data. There are many different ways of how to compare two things. For example, by comparing apple and tomatos. Some may compare their size, some may compare their taste.

Upvotes: 2

Hart CO
Hart CO

Reputation: 34774

Your INNER JOIN would only return matching records, you could use a FULL JOIN and WHERE criteria to check for non-matching records:

SELECT * 
FROM #Temp a
FULL JOIN AccountTable b
   ON a.ACCOUNT = b.ACCOUNT
     AND  a.SERV_ACCT = b.SERV_ACCT
     AND  a.INV_DATE = b.INV_DATE
     AND  a.CURR_Date = b.CURR_Date
WHERE a.Account IS NULL
   OR b.Account IS NULL

You could combine that with a CASE statement to get a breakdown of records matching:

SELECT CASE WHEN a.Account IS NULL THEN 'Record in B Only'
            WHEN b.Account IS NULL THEN 'Record in A Only'
            ELSE 'Record in Both'
       END
       ,COUNT(*) 'CT'     
FROM #Temp a
FULL JOIN AccountTable b
   ON a.ACCOUNT = b.ACCOUNT
     AND  a.SERV_ACCT = b.SERV_ACCT
     AND  a.INV_DATE = b.INV_DATE
     AND  a.CURR_Date = b.CURR_Date
GROUP BY CASE WHEN a.Account IS NULL THEN 'Record in B Only'
            WHEN b.Account IS NULL THEN 'Record in A Only'
            ELSE 'Record in Both'
       END
ORDER BY COUNT(*) DESC

Note: Both of the above assume Account can't be legitimately NULL, pick a field that can't be NULL or use multiple fields that can't all be NULL legitimately. IE: 

WHERE COALESCE(a.ACCOUNT, a.SERV_ACCT,a.INV_DATE,a.CURR_Date) IS NULL

Upvotes: 4

Related Questions