Solved

Query 2 tables to view UNCOMMON records.

Posted on 2004-09-07
12
468 Views
Last Modified: 2008-03-03
I am trying to create a query that compares 2 tables and returns the records that are not common to both.  I have tried every combination of INNER, OUTER joins, but can't seem to get it.

For example:

INITIALLY:
Table A records:   record1, record2, record3, record4, record5
Table B records: none.

I insert into Table B:  record 1, record2

I now want the query for Table A to showonly : record3, record4, record5
. . . excluding the 2 records that now exist in Table B

Tnaks in advance.

Maxwell
0
Comment
Question by:MaxwellTurner
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 125 total points
ID: 11999431
Use the 'Find Unmatched Query Wizard by selecting Queries on the left, hit the 'New' button, and choosing 'Find Unmatched Query Wizard' from the dialog box.  Then, follow the graphical UI to select your tables, how they are joined, and what to display.

Hope this helps.
-Jim
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 11999480
Select * from table_a where id not in (select id from table_b)
Union
Select * from table_b where id not in (select id from table_a)
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11999823
Don't use IN if you can help it, as the performance is very poor. It is much more efficient to use joins:

SELECT * FROM TableA LEFT JOIN TableB ON TableA.IDField=TableB.IDField WHERE TableB.IDField IS NULL

This will show all records in TableA which do not exist in Table B. It presumes a primary key called IDField.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 16

Expert Comment

by:Nestorio
ID: 12000097
I would like to do a performance test.

Anyway the solution with a join query would be:

SELECT * FROM TableA LEFT JOIN TableB ON TableA.IDField=TableB.IDField WHERE TableB.IDField IS NULL
UNION
SELECT * FROM TableB LEFT JOIN TableA ON TableA.IDField=TableB.IDField WHERE TableA.IDField IS NULL
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12000163
Nestorio, please do perform your performance test. You'll find that what I said is correct - it is a well known limitation of the IN clause. If you must use a subquery, EXISTS is also a viable alternative.
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 12000430
I tried JH's advice before I got the other comments & it worked exactly how I wanted it to ( I feel kinda dumb, having not noticed that Query Wizard before!!!).  I however, ended up with this query:

SELECT FROM TableA LEFT JOIN TableB ON 'TableA.IDfield'= 'TableB.IDfield'
WHERE (((TableA.Field)=#passed variable#) AND ((TableA.IDfield) Is Null))

. . . I didn't seem to need the UNION and the other SELECT?

I am using this on a webpage built using Coldfusion - 2 frames (left & right) - Basically I am passing the records back & forth between the Left & Right frames & refreshing them each time.  The Left frames displays all the records initially, but when I pass a record to the Right frame, it is displayed there and as the Left frame refreshes, it disappears from that list.

I also pass a variable which filters the query.

Anyways, thanks for all the help.  I am interested in the difference between my query and the suggestions posted by Nestorio.  BTW I am not using IN.

Thanks,
Maxwell
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 12000591
Maxwell,

<<I am trying to create a query that compares 2 tables and returns the records that are not common to both>>

Try with these data sets:

Table A
1
2
3
4

Table B
3
4
5
6

Result (not common to both tables)

1
2
5
6
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 12000655
That is exactly what I am trying to accomplish, but it seems to work without the UNION and extra SELECT (from your post) as I mentioned earlier.  I guess there are 2 ways of doing this.  I get the same results using only:

SELECT * FROM TableA LEFT JOIN TableB ON TableA.IDField=TableB.IDField WHERE TableB.IDField IS NULL


Maxwell
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 12000736
I think your query would return (based on my example):

Result
1
2

But not
1
2
5
6

What is wanted.
Or.. maybe I'm wrong...
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12000763
Nestorio's query returns records in A but not in B, AND records in B but not in A.
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 12001199
Shane,

I did the performance test with:

TableA (2 fields, 100,000 records)
TableB (2 fields, 100,000 records)

I ran 2 queries to get records in TableA that didn't match in TableB.
One query with the clause NOT IN and the other With a Left join clause.
There were 50,000 records that didn't match.

Both queries lasted almost the same, about 25 seconds (with my environment of software and hardware).
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12001244
Try it with more complex queries that perform grouping. The other place where you notice severe performance hits are text fields. Also, up to a point depends how you've written your query.

I'll dig out some articles for you.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

806 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