SQL problem, query locks up machine

  • Thread starter Thread starter david
  • Start date Start date
D

david

I need to solve the following problem. I need to find all
numbers in Table 1 that are not in table 2
Table 1 has 2604 rows and table 2 has 52617 rows.
The numbers in Table 1 are unique whereas in table 2 they
are repeated.
Here is my attempt that always locks up my machine, is
there a better way?

SELECT changerequestID
FROM changerequestwithdata
WHERE changerequestid not in (select changerequestID from
qryfinduniqueactivity);

this is qryfinduniqueactivity:

SELECT DISTINCT changerequestid
FROM activitywithdata;
 
Hi David

It would be much faster to use a "frustrated left join" for this:

Select T1.changerequestid
from changerequestwithdata as T1
left join activitywithdata as T2
on T1.changerequestid=T2.changerequestid
where T2.changerequestid is null;
 
David,

Best method to do this is to join the tables with a non-equi join.

Add both tables to the query grid
Join them on the ChangeRequestID fields
Right click on the line connecting the two tables, select Join
Properties, then select the option that indicates "Include all records
from "changerequestwithdata"....

The SQL should look something like:

Select T1.ChangeRequestID
FROM changerequestwithdata T1
LEFT JOIN activitywithdata T2
ON T1.ChangeRequestID = T2.ChangeRequestID
WHERE T2.ChangeRequestID IS NULL

--
HTH

Dale Fye


I need to solve the following problem. I need to find all
numbers in Table 1 that are not in table 2
Table 1 has 2604 rows and table 2 has 52617 rows.
The numbers in Table 1 are unique whereas in table 2 they
are repeated.
Here is my attempt that always locks up my machine, is
there a better way?

SELECT changerequestID
FROM changerequestwithdata
WHERE changerequestid not in (select changerequestID from
qryfinduniqueactivity);

this is qryfinduniqueactivity:

SELECT DISTINCT changerequestid
FROM activitywithdata;
 
Dear Dale:

Your method is probably fine, but the terminology is a bit twisted.

Your ON clause says the join is where the ChangeRequestIDs are the
same. They are equal. That means your join IS an equi-join.

If this part of the query were to read:

ON T1.ChangeRequestID < T2.ChangeRequestID

then the join would not be an equi-join. Generally, anything but an
equals is not an equi-join.

I think the correct terminology for this is that you are changing the
join from an INNER JOIN to a one-sided, LEFT OUTER JOIN (although the
Jet syntax does not use the term OUTER here, that's what it is.)

Sorry, but using correct terminology is essential to our ability to
communicate in the newsgroup, so I think it is important to use it
correctly.

Sorry if this makes anyone feel I'm making myself the "terminology
police" here. But words are all we have!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

I don't mind the correction at all. I totally agree that correct
syntax, whether in the explaination of the problem, the query or the
explaination of the solution, is critical to understanding.

Thanks.

Don't know where you are located. If in the US, have a Happy
Thanksgiving.

--
HTH

Dale Fye


Dear Dale:

Your method is probably fine, but the terminology is a bit twisted.

Your ON clause says the join is where the ChangeRequestIDs are the
same. They are equal. That means your join IS an equi-join.

If this part of the query were to read:

ON T1.ChangeRequestID < T2.ChangeRequestID

then the join would not be an equi-join. Generally, anything but an
equals is not an equi-join.

I think the correct terminology for this is that you are changing the
join from an INNER JOIN to a one-sided, LEFT OUTER JOIN (although the
Jet syntax does not use the term OUTER here, that's what it is.)

Sorry, but using correct terminology is essential to our ability to
communicate in the newsgroup, so I think it is important to use it
correctly.

Sorry if this makes anyone feel I'm making myself the "terminology
police" here. But words are all we have!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Dear Dale:

I appreciate your attitude. Happy Thanksgiving to you, too!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top