From: Richard Huxton <dev@archonet.com>
To: Marco Roda <MarcoRoda@amdosoft.com>
To: pgsql-sql@postgresql.org
Subject: Re: OUTER JOIN
Date: Wed, 23 Apr 2003 18:55:36 +0100
Message-ID: <200304231855.37050.dev@archonet.com> (raw)
In-Reply-To: <000401c304ec$a77547e0$0301a8c0@amdosoft.local>
References: <000401c304ec$a77547e0$0301a8c0@amdosoft.local>
On Thursday 17 Apr 2003 3:21 pm, Marco Roda wrote:
> Hallo,
>
> I need to port some SQL from Oracle to PostgreSQL v7.2, but I am finding
> problems with outer joins.
[snip]
> and here is the SQL for Oracle:
> SELECT ST.id, ST.order_num, ST.from_time, ST.to_time, ST.disabled,
> SD.description
> FROM shift_type ST, shift_desc SD
> WHERE ST.id = SD.shift_type_id(+) AND SD.app_language_id(+) = 2
> ORDER BY ST.order_num
>
> The expected result should be:
> id | order_num | from_time | to_time | disabled | description
> ----+-----------+-----------+----------+----------+-------------
> 1 | 1 | 06:00:00 | 14:00:00 | f |
> 2 | 2 | 14:00:00 | 22:00:00 | f |
> 3 | 3 | 22:00:00 | 06:00:00 | t |
> 6 | 6 | 00:00:00 | 23:00:00 | f | SHIFT_DE
> with description populated with NULL, as app_language_id not found.
>
> It seems that Oracle's outer joins work on the basis the single record,
> while PostgreSQL don't.
> How to do it?
SELECT ST.id, ST.order_num, ST.from_time, ST.to_time, ST.disabled,
SD.description
FROM shift_type ST LEFT JOIN shift_desc SD
ON ST.id=SD.shift_type_id AND SD.app_language_id=2
ORDER BY ST.order_num;
That seems to work. Out of curiosity, what were you trying?
--
Richard Huxton
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: dev@archonet.com, MarcoRoda@amdosoft.com
Subject: Re: OUTER JOIN
In-Reply-To: <200304231855.37050.dev@archonet.com>
* 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