Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

nested query interpretation

Posted on 2010-11-22
9
Medium Priority
?
242 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 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