Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7GLiYP48636 for ; Thu, 16 Aug 2001 17:44:34 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 103647; Thu, 16 Aug 2001 14:44:31 -0700 From: "Josh Berkus" Subject: Re: Nested JOINs To: Oleg Lebedev , pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Thu, 16 Aug 2001 14:44:31 -0700 Message-ID: In-Reply-To: <3B7C2CFA.2B57616C@waterford.org> MIME-Version: 1.0 Content-Type: text/plain; charset="KOI8-R" Content-Transfer-Encoding: 8bit X-Archive-Number: 200108/224 X-Sequence-Number: 4456 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