Date: Sun, 02 May 2004 17:44:36 +0100
From: Adam Witney <awitney@sghms.ac.uk>
Subject: Re: double left outer join on the same table
In-reply-to: <4095207C.8080009@numerixtechnology.de>
To: mailreg@numerixtechnology.de,
	pgsql-sql <pgsql-sql@postgresql.org>
Message-id: <BCBAE404.321CF%awitney@sghms.ac.uk>
MIME-version: 1.0
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
User-Agent: Microsoft-Entourage/10.1.1.2418

On 2/5/04 5:23 pm, "T E Schmitz" <mailreg@numerixtechnology.de> 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


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.