[AccessD] SQL in-line subquery

You can use an in-line subquery as if it were a table or a saved query:

SELECT T1.* FROM Table1 AS T1 INNER JOIN  [SELECT DISTINCT Table2.ID 
FROM Table2]. AS T2 on  T1.ID = T2.ID;

Does that explain it?  You have to use the square brackets followed by a
period to wrap the in-line subquery.  If you ever find it in help, for
goodness sake, post the reference here! 

Charlotte Foust

-----Original Message-----
From: Andy Lacey [mailto:andy at minstersystems.co.uk] 
Sent: Tuesday, May 06, 2003 11:37 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] SQL in-line subquery


Can I second that question? As you say, looking up subqueries in help
doesn't tell me. What do you mean?

Andy Lacey
http://www.minstersystems.co.uk



> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
> Sent: 06 May 2003 17:30
> To: 'accessd at databaseadvisors.com'
> Subject: RE: [AccessD] SQL in-line subquery
> 
> 
> What's the [...] syntax?
> 
> Drew
> 
> -----Original Message-----
> From: Charlotte Foust [mailto:cfoust at infostatsystems.com]
> Sent: Tuesday, May 06, 2003 11:19 AM
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] SQL in-line subquery
> 
> 
> As I recall, the help files did NOT include anything on the
> [...] syntax.  I stumbled over that myself in 97 but never 
> found anything in the help files except on regular subqueries.
> 
> Charlotte Foust
> 
> -----Original Message-----
> From: Drew Wutka [mailto:DWUTKA at marlow.com]
> Sent: Tuesday, May 06, 2003 8:09 AM
> To: 'accessd at databaseadvisors.com'
> Subject: RE: [AccessD] SQL in-line subquery
> 
> 
> Actually, 'in-line' queries or subqueries are described in
> Access 97's help files.  Just look up SQL Subqueries, or just 
> subqueries.
> 
> Drew
> 
> -----Original Message-----
> From: Gustav Brock [mailto:gustav at cactus.dk]
> Sent: Tuesday, May 06, 2003 10:12 AM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] SQL in-line subquery
> 
> 
> Hi Jim
> 
> > <Snip>
> > Those not familiar with the strange [..]. syntax may look up the 
> > archives for "SQL in-line subquery" <Snip>
> 
> > Since I was (am) unfamiliar with this concept, I did look it up. I 
> > have a few questions.
> 
> > 1.  Does this method run faster than a nested query?
> 
> A saved and compiled nested query may run faster. Haven't
> done any testing though.
> 
> > 2.  What are the pitfalls?
> 
> One is that no documentation is available as far as I know.
> 
> > 3.  What are the benefits?
> 
> That you can avoid temporary tables or subqueries. All code
> is in one query which is an advantage if you build it from VBA code.
> 
> /gustav
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/a> ccessd
> Website: 
> http://www.databaseadvisors.com 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/a> ccessd
> Website: 
> http://www.databaseadvisors.com 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/a> ccessd
> Website: 
> http://www.databaseadvisors.com 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/a> ccessd
> Website: 
> http://www.databaseadvisors.com
> 

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com