Re: Correlated Query problem with large tables
- From: "david epsom dot com dot au" <david@epsomdotcomdotau>
- Date: Tue, 6 Jun 2006 20:59:36 +1000
Yes, in that case go for a join. Jet tends to be optimised
for the ANSI joins.
(david)
"Cadences" <Cadences@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B6F0BE0E-D361-4F1E-BC37-7B60A026AB08@xxxxxxxxxxxxxxxx
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
"david epsom dot com dot au" wrote:
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)
"Cadences" <Cadences@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:77B35751-ADB6-467D-AC8D-ACDF82E215ED@xxxxxxxxxxxxxxxx
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
.
- References:
- Re: Correlated Query problem with large tables
- From: david epsom dot com dot au
- Re: Correlated Query problem with large tables
- Prev by Date: Re: dynamic SQL (FROM clause is killing me)
- Next by Date: Re: dynamic SQL (FROM clause is killing me)
- Previous by thread: Re: Correlated Query problem with large tables
- Next by thread: Sorting field with alpha numeric data
- Index(es):
Relevant Pages
|
