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 ON c.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 information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco