query hangs when I add not to exists

[alexalex]

I have a query with just only 2 tables. Tables are related as one to many by ID field.
I use subquery which gonna check all rows with same ID from second query.
When I use exists predicate - everything works, when I add not to exists - quesry hangs...

select ID1 from table1 where table1.numfield>0 and not exists (SELECT ID2 FROM table2 where table2.ID2=table1.ID1 and (Status>0 or inDate is null or outDate is nul

l)); I'v get recomendation to use instead

select ID1  from table1  left join table2  on (table2.ID2=table1.ID1 and (table2.Status>0 or table2.inDate is null or table2.outDate is null)) where table1.numfield>0   and table2.ID2 is null

But this code returned for me records from table1 which don't have records with same ID in table2

 

[boblarson]

Change the word LEFT to INNER.

 

[alexalex]

or to RIGHT?

 

[boblarson]

or to RIGHT?

Not unless you want all records from table two and only those that match in table one. If you want only records from table two which match the ID in table one, then an inner join is what you need.

 

[Pat Hartman]

Don't use a subquery when a Join will work. They are very inefficient in Jet/ACE. In this case you want a left join and the where clause will look for null in the PK of the "right" table.