Solved

How to account for the difference between 2 tables records using SQL Server 2005?

Posted on 2013-11-10
1
358 Views
Last Modified: 2013-11-10
I am trying to reconcile the difference between 2 tables using SQL Server 2005.
The tables are titled dbo.tblCustomersNew and dbo.tblProductsNew.

There is a 1 to many relationship between dbo.tblCustomersNew and dbo.tblProductsNew

Here is what I have found out so far:

select count(*) from dbo.tblProductsNew                   result set is 208565 records
----------------------------------------------------------------------------------------------------

SELECT Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number]
FROM dbo.tblCustomersNew AS C
INNER JOIN dbo.tblProductsNew AS P
ON   C.CustomerNumber = P.CustomerNumber
AND C.OfficeNumber      = P.OfficeNumber  
               
---------------------------------------------result set is 207206 records Matching records            
----------------------------------------------------------------------------------------------------

208565 minus 207206 = 1359 records difference
----------------------------------------------------------------------------------------------------
SELECT CustomerNumber, OfficeNumber FROM dbo.tblProductsNew
WHERE  CustomerNumber not in (SELECT C.CustomerNumber
FROM dbo.tblCustomersNew AS C
INNER JOIN dbo.tblProductsNew AS P
ON  C.CustomerNumber = P.CustomerNumber
AND C.OfficeNumber   = P.OfficeNumber)                    result set is 782 records
0
Comment
Question by:zimmer9
1 Comment
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39637630
To find missing CustomersNew entries you need to use left join with a where clause, like this:
SELECT P.CustomerNumber, P.OfficeNumber
FROM dbo.tblProductsNew AS P
LEFT JOIN dbo.tblCustomersNew AS C
ON   C.CustomerNumber = P.CustomerNumber
AND C.OfficeNumber      = P.OfficeNumber
WHERE C.CustomerNumber IS NULL

Open in new window

To find missing ProductsNew entries use right join
SELECT Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number]
FROM dbo.tblProductsNew AS P
RIGHT JOIN dbo.tblCustomersNew AS C
ON   C.CustomerNumber = P.CustomerNumber
AND C.OfficeNumber      = P.OfficeNumber
WHERE P.CustomerNumber IS NULL

Open in new window

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to run sql statements from SQLCMD or command line 2 59
Pivot Query Problem 9 41
Truncate vs Delete 63 105
MS SQL Server - Looking to filter rows based on column value 3 37
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question