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