Re: double left outer join on the same table

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
> 
> 
> 
>