Solved

Need assistance with a Query where records do not match.

Posted on 2014-01-27
2
312 Views
Last Modified: 2014-02-11
Hi Experts,
I need help with a query.  I need to find records in TABLE_A that don't match records in TABLE_B.  The qualifying criteria is where TABLE_A.RECORDNUMBER, TABLE_A.AMOUNT, and TABLE_A.DATE do not match TABLE_B.RECORDNUMBER, and TABLE_B.AMOUNT, and TABLE_B.DATE.  How can I accomplish this?  Thanks in advance.

mrotor
0
Comment
Question by:mainrotor
2 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 39812853
SELECT a.*
FROM TABLE_A a
   -- the LEFT JOIN insures that all rows in Table_A are in the result set...
   LEFT JOIN TABLE_B b ON a.RECORDNUMBER = b.RECORDNUMBER
      AND a.AMOUNT = b.AMOUNT
      AND a.DATE = b.DATE
-- ... where there is not a match in all three keys
WHERE (b.RECORDNUMBER IS NULL OR b.AMOUNT IS NULL OR b.DATE IS NULL)
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 250 total points
ID: 39813471
what Jim Horn suggested will definetly work, as in the case of most of the problems there is an alternative solution available for this as well

Alternative solution

select * from Table_A A
WHERE NOT EXISTS ( SELECT 1 FROM TABLE_B B 
WHERE B.RecordNumber  = A.RecordNumber and B.amount = A.Amount and B.date = A.date
)

Open in new window

0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now