?
Solved

nested query interpretation

Posted on 2010-11-22
9
Medium Priority
?
241 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Technology Partners: 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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

771 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