• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

Need assistance with a Query where records do not match.

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
mainrotor
Asked:
mainrotor
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Surendra NathCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now