pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddouble left outer join on the same table
3+ messages / 2 participants
[nested] [flat]
* double left outer join on the same table
@ 2004-05-02 16:23 T E Schmitz <mailreg@numerixtechnology.de>
2004-05-02 16:44 ` Re: double left outer join on the same table Adam Witney <awitney@sghms.ac.uk>
0 siblings, 1 reply; 3+ messages in thread
From: T E Schmitz @ 2004-05-02 16:23 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: double left outer join on the same table
2004-05-02 16:23 double left outer join on the same table T E Schmitz <mailreg@numerixtechnology.de>
@ 2004-05-02 16:44 ` Adam Witney <awitney@sghms.ac.uk>
2004-05-02 17:09 ` Re: double left outer join on the same table T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 1 reply; 3+ messages in thread
From: Adam Witney @ 2004-05-02 16:44 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: double left outer join on the same table
2004-05-02 16:23 double left outer join on the same table T E Schmitz <mailreg@numerixtechnology.de>
2004-05-02 16:44 ` Re: double left outer join on the same table Adam Witney <awitney@sghms.ac.uk>
@ 2004-05-02 17:09 ` T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 0 replies; 3+ messages in thread
From: T E Schmitz @ 2004-05-02 17:09 UTC (permalink / raw)
To: Adam Witney <awitney@sghms.ac.uk>; +Cc: pgsql-sql
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
>
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-05-02 17:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-02 16:23 double left outer join on the same table T E Schmitz <mailreg@numerixtechnology.de>
2004-05-02 16:44 ` Adam Witney <awitney@sghms.ac.uk>
2004-05-02 17:09 ` T E Schmitz <mailreg@numerixtechnology.de>
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