Solved

Query 2 tables to view UNCOMMON records.

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

Technology Partners: 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!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

752 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