Solved

nested query interpretation

Posted on 2010-11-22
9
238 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Email question 19 91
unknown computers in my LAN 12 99
Using a combo box to search a form. 3 35
Access date picker/Monday dates only 3 12
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

732 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