Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query 2 tables to view UNCOMMON records.

Posted on 2004-09-07
12
Medium Priority
?
515 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
[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
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 66

Accepted Solution

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

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

670 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