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
