Solved

nested query interpretation

Posted on 2010-11-22
9
235 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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:dinudany
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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now