From: T E Schmitz <mailreg@numerixtechnology.de>
To: Adam Witney <awitney@sghms.ac.uk>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: double left outer join on the same table
Date: Sun, 02 May 2004 18:09:30 +0100
Message-ID: <40952B4A.6090300@numerixtechnology.de> (raw)
In-Reply-To: <BCBAE404.321CF%awitney@sghms.ac.uk>
References: <BCBAE404.321CF%awitney@sghms.ac.uk>
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" <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
>
>
>
>
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: mailreg@numerixtechnology.de, awitney@sghms.ac.uk
Subject: Re: double left outer join on the same table
In-Reply-To: <40952B4A.6090300@numerixtechnology.de>
* 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