[SQL] double left outer join on the same table
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 -- Regards, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] double left outer join on the same table
On 2/5/04 5:23 pm, "T E Schmitz" <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] 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" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
