Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need assistance with a Query where records do not match.

Posted on 2014-01-27
2
Medium Priority
?
337 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 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 1000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

618 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