Solved

Query 2 tables to view UNCOMMON records.

Posted on 2004-09-07
12
459 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
 
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
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.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

895 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

19 Experts available now in Live!

Get 1:1 Help Now