Correlated Query problem with large tables

[Guest]

I'm new to newsgroups, I've been struggling with this problem.
Any help appreciated.

Orders is a distinct list of Customers + Stock Numbers showing what they've
ordered. ReducedStockList are priority items.

I'm trying find out stock numbers from ReducedStockList that Customers
haven't ordered i.e not in the Orders table

The query below works with an Orders table of a few hundred rows but
doesn't work with > 10000 rows. Suspect problem is with the use of 'not in'

SELECT DISTINCT a.[Branch Ac], b.CatNumber
FROM Orders AS a, ReducedStockList AS b
WHERE (((b.CatNumber) Not In (select catnumber from Orders where [Branch Ac]
= a.[Branch Ac])))
ORDER BY a.[Branch Ac], b.CatNumber;

can anyone help?
Thanks

 

[david epsom dot com dot au]

What do you mean by 'doesn't work'?

BTW, Access/Jet prefers you to do a Join instead of correlated subquery.
Unless you have some reason, you should consider just re-writing that
as a join between a and b.

(david)

 

[Guest]

Sorry, being vague: Doesn't work = The query runs for hours without
producing a result. I get the green 'Running Query' message which fills to
the right but the query never finsihes.
Correlated, as I'd used it before in Oracle a long time ago.
I'll have a look at Joins.
Thanks for your help
Steve

What do you mean by 'doesn't work'?

BTW, Access/Jet prefers you to do a Join instead of correlated subquery.
Unless you have some reason, you should consider just re-writing that
as a join between a and b.

(david)

I'm new to newsgroups, I've been struggling with this problem.
Any help appreciated.

Orders is a distinct list of Customers + Stock Numbers showing what
they've
ordered. ReducedStockList are priority items.

I'm trying find out stock numbers from ReducedStockList that Customers
haven't ordered i.e not in the Orders table

The query below works with an Orders table of a few hundred rows but
doesn't work with > 10000 rows. Suspect problem is with the use of 'not
in'

SELECT DISTINCT a.[Branch Ac], b.CatNumber
FROM Orders AS a, ReducedStockList AS b
WHERE (((b.CatNumber) Not In (select catnumber from Orders where [Branch
Ac]
= a.[Branch Ac])))
ORDER BY a.[Branch Ac], b.CatNumber;

can anyone help?
Thanks

 

[david epsom dot com dot au]

Yes, in that case go for a join. Jet tends to be optimised
for the ANSI joins.

(david)

Sorry, being vague: Doesn't work = The query runs for hours without
producing a result. I get the green 'Running Query' message which fills to
the right but the query never finsihes.
Correlated, as I'd used it before in Oracle a long time ago.
I'll have a look at Joins.
Thanks for your help
Steve

What do you mean by 'doesn't work'?

BTW, Access/Jet prefers you to do a Join instead of correlated subquery.
Unless you have some reason, you should consider just re-writing that
as a join between a and b.

(david)

I'm new to newsgroups, I've been struggling with this problem.
Any help appreciated.

Orders is a distinct list of Customers + Stock Numbers showing what
they've
ordered. ReducedStockList are priority items.

I'm trying find out stock numbers from ReducedStockList that Customers
haven't ordered i.e not in the Orders table

The query below works with an Orders table of a few hundred rows but
doesn't work with > 10000 rows. Suspect problem is with the use of 'not
in'

SELECT DISTINCT a.[Branch Ac], b.CatNumber
FROM Orders AS a, ReducedStockList AS b
WHERE (((b.CatNumber) Not In (select catnumber from Orders where
[Branch
Ac]
= a.[Branch Ac])))
ORDER BY a.[Branch Ac], b.CatNumber;

can anyone help?
Thanks