Re: Nested JOINs

Oleg,

> Below is the query I am trying to execute and the error I am getting:
> SELECT media
> FROM    (dtrowmedia a INNER JOIN dtrow b ON (a.dtrow = b.objectid)) c
>                 INNER JOIN dtcol d ON (c.dtcol = d.objectid)

First, the "INNER" keyword is not required on Postgres; you may omit it
with impunity.  (If you are doing this on MS SQL Server or MS Access and
have joined this list by mistake, our advice won't be very useful to
you).

Second, you can't alias a JOINed set of tables; you may alias a
subselect, or alias a table:
FROM dtrow b   ... is legal
FROM (SELECT * FROM dtrow) b   ... is also legal
FROM (dtrow JOIN dtrowmedia ON dtrow.objectid = dtrowmedia.dtrow) b
... is not legal.

Thus, the query above is best expressed simply as:

SELECT media
FROM dtrowmedia a JOIN dtrow b ON a.dtrow = b.objectid        JOIN dtcol d ON b.dtcol = d.objectid;

If you actually did need to put some extra criteria into the first join,
then you would use a subselect:

SELECT media
FROM (SELECT * FROM dtrowmedia a JOIN dtrow b ON a.dtrow = b.objectid          WHERE dtrow > 3000) c       JOIN dtcol d
ONc.dtcol = d.objectid;
 

But keep in mind in this case that you cannot reference a. or b. in the
SELECT list at the top, just c. because a. and b. exist only in the
subselect.

Now, go out and buy a copy of "SQL for Smarties".  You'll be glad you
did.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco