Solved

Query to find delta / exceptions between 2 tables

Posted on 2014-12-04
14
184 Views
Last Modified: 2014-12-04
I have two tables (tableA, tableB) which basically contain the same data.  The key between the 2 is serial_number.  I need a query to return the delta between the 2 tables.

TableA contains all the data that should be in TableB but that is not the case.  I need to determine which serial numbers from TableA are not appearing in TableB.
0
Comment
Question by:szadroga
[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
  • 6
  • 2
  • 2
  • +3
14 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40480722
Hello,

try to use EXCEPT command..
SELECT *
FROM Table A
EXCEPT
SELECT *
FROM TableB

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40480738
SELECT serial_number
FROM tableA
EXCEPT
SELECT serial_number
FROM tableB
0
 

Author Comment

by:szadroga
ID: 40480744
great, so is that basically a translation of

select * from TableA where SERIAL_NUMBER does not equal (select serial_number from TableB);
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:szadroga
ID: 40480760
I want to return more fields in the query, not just serial_number but serial_number is the field I need to base the "delta" logic on.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40480768
I don't think either of ours are that exactly.

Haris compares the entirety of all of the fields, and only where all the fields are identical in both tables for a particular row, then exclude that.

Mine shows only the serial_number, and excludes tableB's serial numbers; it doesn't show all the fields.

If you want to show everything (as you have said "Select *"), then

select * from TableA where SERIAL_NUMBER not in (select serial_number from TableB);
0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40480781
I prefer the NOT EXISTS clause:
SELECT *
FROM TableA
WHERE NOT EXISTS (SELECT 1 
              FROM TableB
              WHERE TableB.serial_number=TableA.serial_number)

Open in new window

0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40480861
Or, you can use a left join.
Select *
From tblA Left Join tblB On tblA.SerialNumber = tblB.SerialNumber
Where tblB.SerialNumber is Null;

"EXCEPT" is not valid Access SQL Syntax.
0
 

Author Comment

by:szadroga
ID: 40480884
Vitor,

What does the SELECT 1 represent in the sub SELECT query?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40480925
SELECT clause needs at least a column or value. 1 is a constant value so it's used to avoid access to data. It's an old trick ;)
Could be SELECT NULL or SELECT 'A' or any constant value. It's better than a SELECT * since you won't need the sub-query to return any data.
0
 

Author Comment

by:szadroga
ID: 40480928
thanks for clarifying.  I will keep that trick in mind going forward.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40481227
Is the data in an Access database or a SQL Server database?  If an Access, the EXCEPT keyword is not supported. (confirmed Pat's comment)

You can do this in a single UNION query.
Example:
Select tblA.*, "In A Not In B" As tblDiff
From tblA Left Join tblB On tblA.SerialNumber = tblB.SerialNumber
Where tblB.SerialNumber is Null
UNION ALL
Select tblB.*, "In B Not In A" As tblDiff
From tblB Left Join tblA On tblA.SerialNumber = tblB.SerialNumber
Where tblA.SerialNumber is Null
UNION ALL
Select tblA.*, "Field difference" As tblDiff
From tblA Join tblB On tblA.SerialNumber = tblB.SerialNumber
Where tblA.Field1 <> tblB.Field1
OR tblA.Field2 <> tblB.Field2
OR tblA.Field3 <> tblB.Field3
OR tblA.Field4 <> tblB.Field4

Open in new window

0
 

Author Comment

by:szadroga
ID: 40481329
Currently in Access, but I will move to SQL if the report i am building seems beneficial.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40481340
The suggestion I made of the outer join and the suggestion of Not Exists will work in both variants of SQL.  But if you are going to use T-SQL (with a pass-through query) you also have the option of "EXCEPT".
0
 

Author Closing Comment

by:szadroga
ID: 40481429
worked perfectly and at an optimal speed.
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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Viewers will learn how the fundamental information of how to create a table.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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