[AccessD] SQL in-line subquery

I've noticed the phenomenon over the years that all the mags on the same
general topic will tend to have similar articles at about the same time.
I think it must be ESP or aliens!  

Charlotte Foust

-----Original Message-----
From: John Bartow [mailto:john at winhaven.net] 
Sent: Thursday, May 08, 2003 8:46 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] SQL in-line subquery


Ah, man on a mission - I found the issue of Access Advisor.

It was December 2001 Advisor Tips

"Creating Derived Tables in Jet"

Access 2002/200/97

SQL Server lets you create derived tables, which are SELECT statements
that appear in the FROM clause of a query. In other words, you can
select from the resultset returned by a nested SELECT  statement. There
is no documented way to accomplish this in a Jet query without using a
saved nested query, but there is a, but there is a little-known synatx
that makes it possible. The following query runs in Access 97 and uses a
derived table named "GIO" which is not a saved object. The derived table
must be enclosed in square brackets and terminated with a period:
[<Select Staement>].

SELECT Products.ProductName, Products.UnitPrice, Gio.CatAvg,
Categories.CategoryName FROM (Products INNER JOIN [SELECT
Products.CategoryID, Categories.CategoryName, Avg(Products.UnitPrice) AS
CatAvg FROM Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID GROUP BY Products.CategoryID,
Categories.CategoryName]. AS Gio ON Gio.CategoryID =
Products.CategoryID) INNER JOIN Categories ON Products.CategoryID =
Categories.CategoryID ORDER BY Products.ProductID;

-Giovanni Caruso, via Andy Barron, Contributing Editor

Open Northwind DB and paste the listed select statement into the SQL
window of a query, save it and then run it to see the results. Don't try
to run before saving though because the query design grid will mess it
up really bad.

I checked the archives of every other access list I know of and other
than "Paul's" response to Gustav on AccessD in mid 2001 it is the
earliest reference to this syntax I can find. Given that Giovanni's tip
was probably submitted months before it was actually published it would
be hard to know which was first. It could be coincidence that both of
them originated at roughly the same time. But then again, I have seen a
lot of tips in newsletters and magazines, from both readers and staff,
that seem to arrive shortly after an item was discussed on one list or
another! Maybe Giovanni saw the tip here and really wanted one of those
t-shirts from Access Advisor!

JB



> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
> Sent: Thursday, May 08, 2003 10:15 AM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] SQL in-line subquery
>
>
> Hi Lambert
>
> > It seems to me that the [Select * from Foo].  syntax allows one
> to create
> > queries in Access that use a sub-query as the base table. In
> other words you
> > don't need a temporary table or an already saved query. This is
> something
> > I've often wished I could do, if only the [ ]. syntax was documented

> > somewhere.
>
> > Which begs the question - just how did this special syntax get
> discovered?
> > It always intrigues me when someone pops out of the woods and
> says "hey look
> > at this undocumented feature..."
>
> It was someone called Paul in July 2001 that mentioned it as common 
> knowledge ...
>
> /gustav
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>


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