From: Josh Berkus <josh@agliodbs.com>
To: Oleg Lebedev <olebedev@waterford.org>
To: pgsql-sql@postgresql.org
Subject: Re: Nested JOINs
Date: Thu, 16 Aug 2001 14:44:31 -0700
Message-ID: <web-103647@davinci.ethosmedia.com> (raw)
In-Reply-To: <3B7C2CFA.2B57616C@waterford.org>
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@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, olebedev@waterford.org
Subject: Re: Nested JOINs
In-Reply-To: <web-103647@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox