Solved

MINUS (set diff.) operator in Access query

Posted on 2004-04-25
2
4,292 Views
Last Modified: 2007-12-19
I have to retrive records from a table that are not present in another table with the same schema.  But I am working with access database and as much I know it doesn't support MINUS operator.  I use following sql query instead.

SELECT name FROM ref  WHERE not exists (SELECT name FROM glossary WHERE ref.name=glossary.name)

ref has 6571 records
glossary has 11315 records

It's extremely slow.  It took about 2'48'' to return 124 records on a PIII 866 machine.

Is there any other good way?

Thanks in advance.

Jerry
0
Comment
Question by:Jerryleo
[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
2 Comments
 
LVL 6

Accepted Solution

by:
nathaniel earned 50 total points
ID: 10915606
say you have a ref table with (ID, name, file)
and glossary table (ID, name, page)

the faster way to identify is like this example:

SELECT ref.name, FROM ref LEFT JOIN glossary ON ref.ID = glossary.ID
WHERE (glossary.name) Is Null;

this will display all the names from ref table that are not found in glossary table, based on their ID. You can also use the 'name' in joining, instead of their IDs.

hope that helps

0
 

Author Comment

by:Jerryleo
ID: 10924175
Thank you very much

It works great. It only took 0.5' on my PIII 866 machine.

Best Regard

Jerry
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

696 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