Need Help with Query for Comparing Two Tables

[VAST39]

Hi Guys,

I have two tables and I want to write a query that will list all the records that are in my 1099_PER table, but do not exist in the original table. The original has about 18,100 records while the 1099_PER table has about 18,430.

I wrote this query:

SELECT   A.SSN, A.Name4 
FROM     [1099_PER] A
         LEFT OUTER JOIN original B ON A.SSN=B.SSN
        where B.SSN is NULL

It only gave me 68 records which concerned me because there is like a 300 count difference between the two tables. However, I discovered the source of the problem. I am doing a comparison on the SSN and both tables may contain the same SSN, but the larger table contains multiple records of the SSN. I found one SSN that had four records in the 1099 table, but only two records in the original. Even worse, these records are not duplicates. Each record has a different dollar amount.


So I need to find some way to compare the tables and account for multiple instances of the same SSN. If one table contains 4 records of the same SSN, and the other one only has two then I want my query to list the two records missing from the original. IS there any way to do this??

 

[SQLBill]

You have two different issues with data. 1 - you have data that is missing (4 rows in one table for a specific SSN and only 2 in the other table) and 2 - you have data that is different (same amount of rows, but different values for a specific SSN).

So, what do you want your query to tell you? Just which SSN's don't have an equal number of rows? Which SSN's have different values? Or both?

The issue as I see it is that in situation #1, one table has 4 rows and the other only has 2. What if those one or both of those two rows don't match any of the 4 rows? What do you want returned?

Providing some sample data and what you would like to see returned would help us a lot.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[VAST39]

Hi Bill,

My ultimate goal is to have query results that show me every record that is missing in the original table.

So far example, the 1099_PER table has:


123445757 WALTERS $234.00
123445757 WALTERS $123.00
123445757 WALTERS $95.00
123445757 WALTERS $23.22

and the original only has:

123445757 WALTERS $234.00
123445757 WALTERS $123.00


Then I need my query to only report these records since they are missing in the original table:

123445757 WALTERS $95.00
123445757 WALTERS $23.22

 

[gmmastros]

All you *should* need to do is to add another condition to the join. Something like this...

SELECT   A.SSN, A.Name4 
FROM     [1099_PER] A
         LEFT OUTER JOIN original B ON A.SSN=B.SSN
              [!]And A.DollarAmountColumn = B.DollarAmountColumn[/!]
        where B.SSN is NULL

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

What will you do in this circumstance?

I found one SSN that had four records in the 1099 table, but only two records in the original. Even worse, these records are not duplicates. Each record has a different dollar amount.

That sounds like you have records like this:

1099_PER table

123456789 JONES $234.00
123456789 JONES $123.00
123456789 JONES $95.00
123456789 JONES $23.22

and the original only has:

123456789 JONES $546.00
123456789 JONES $12.00

If the records don't match....what do you want returned?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[VAST39]

I don't know why it didn't dawn on me to add a 2nd condition. George's solution worked great. I really appreciate the help, guys.