?
Solved

nested query interpretation

Posted on 2010-11-22
9
Medium Priority
?
244 Views
Last Modified: 2012-05-10
Is it true that "Select * From sometable Where idRecord IN(Select numOrder From tblOrders Where....);" - is interpreted litterally by jet as: "Select * From sometable Where idRecord IN(1000,1001,1002,1003,1004,1005,...);" !? - Hence the 'query too long' errors may appear sometimes?
0
Comment
Question by:NNOAM1
  • 7
9 Comments
 
LVL 17

Expert Comment

by:JezWalters
ID: 34186577
If your tblOrders sub-query returns (1000,1001,1002,1003,1004,1005,...), then the simple answer is yes!  :-)
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 34186588
Depending on what you're using the resultant records for (specifically, if you need the recordset to be updatable or not), you can probably improve performance by using a join instead.
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 34186608
Your question doesn't give a complete query so I can't either, but I was thinking along these lines:
SELECT sometable.*
FROM sometable, tblOrder
WHERE sometable.idRecord = tblOrders.numOrder AND
tblOrders....

Open in new window

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 17

Expert Comment

by:JezWalters
ID: 34186631
... or if you prefer using the JOIN keyword:
SELECT sometable.*
FROM sometable INNER JOIN tblOrder
ON sometable.idRecord = tblOrder.numOrder
WHERE tblOrder....

Open in new window

0
 
LVL 17

Expert Comment

by:JezWalters
ID: 34186661
Access is quite poor at optimising queries that contain sub-queries, and will typically evaluate the sub-query for EVERY record returned by its parent query - even when the sub-query records are identical each time.
0
 

Author Comment

by:NNOAM1
ID: 34186766
The point is - I need the parent query to be updatable!
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 34186779
You should get an updateable query if you use the JOIN keyword (as in id 34186631 above).
0
 
LVL 2

Expert Comment

by:Dinesh Subramanian
ID: 34187217
hi,
the best solution is to use the join query. (as in id 34186631 above).
0
 
LVL 17

Accepted Solution

by:
JezWalters earned 2000 total points
ID: 34187228
You could try re-phrasing your query using the EXISTS predicate too, but I doubt you'll get much of a performance difference.

INNER JOIN is your best bet!  :-)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

850 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