X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 46FD5D1EDF6 for ; Sun, 2 May 2004 14:12:41 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 50146-07 for ; Sun, 2 May 2004 14:12:21 -0300 (ADT) Received: from msgdirector3.onetel.net.uk (msgdirector3.onetel.net.uk [212.67.96.159]) by svr1.postgresql.org (Postfix) with ESMTP id C3E5AD1B4F6 for ; Sun, 2 May 2004 14:12:18 -0300 (ADT) Received: from bolek.coachhouse (213-78-96-26.friaco.onetel.net.uk [213.78.96.26]) by msgdirector3.onetel.net.uk (Mirapoint Messaging Server MOS 3.3.6-GR) with ESMTP id CDJ16401; Sun, 2 May 2004 18:09:37 +0100 (BST) Received: (Exim 3.35) #1 (Debian)) protocol: esmtp id 1BKKT4-0002Ry-00 ; Sun, 02 May 2004 18:09:30 +0100 Message-ID: <40952B4A.6090300@numerixtechnology.de> Date: Sun, 02 May 2004 18:09:30 +0100 From: T E Schmitz Reply-To: mailreg@numerixtechnology.de User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Adam Witney Cc: pgsql-sql Subject: Re: double left outer join on the same table References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.4 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200405/6 X-Sequence-Number: 17720 Hi Adam, this is absolute magic getting a reply within 1/2 hour and a working solution at that! Here's the correct syntax : SELECT SECTION.SECTION_PK, SECTION.SECTION_NAME, SECTION.BRAND_1_FK, SECTION.BRAND_2_FK, b1.BRAND_NAME, b2.BRAND_NAME FROM SECTION left outer join BRAND as b1 on b1.BRAND_PK =BRAND_1_FK left outer join BRAND as b2 on b2.BRAND_PK =BRAND_2_FK Kind Regards, Tarlika Elisabeth Schmitz Adam Witney wrote: > On 2/5/04 5:23 pm, "T E Schmitz" wrote: > > >>Hello, >> >>I have two tables SECTION and BRAND. SECTION is related to BRAND via two >>foreign keys. I would like to select ALL SECTIONs whether the FKs are >>null or not and fetch the BRAND attributes in one SQL statement. In >>other words I need a double outer join. >> >>Is this possible at all? >> >>The following doesn't work for two reasons: >>a) Table name "brand" specified more than once. >>b) how would I specify the same output columns twice? >> >>SELECT >>SECTION.SECTION_PK, >>SECTION.SECTION_NAME, >>SECTION.BRAND_1_FK, >>SECTION.BRAND_2_FK, >>BRAND.BRAND_PK, >>BRAND.BRAND_NAME >> >>FROM SECTION >>left outer join BRAND on BRAND_PK =BRAND_1_FK >>left outer join BRAND on BRAND_PK =BRAND_2_FK >> > > > I don't know if this will solve your specific problem, but you can use the > same table twice in the same query by using aliases, something like this > (untested of course) > > SELECT > SECTION.SECTION_PK, > SECTION.SECTION_NAME, > SECTION.BRAND_1_FK, > SECTION.BRAND_2_FK, > a.BRAND_PK, > a.BRAND_NAME > b.BRAND_PK, > b.BRAND_NAME > > FROM SECTION > left outer join BRAND a on BRAND_PK =BRAND_1_FK > left outer join BRAND b on BRAND_PK =BRAND_2_FK > > > >