Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: "Marco Roda" <MarcoRoda@amdosoft.com>, <pgsql-sql@postgresql.org>
Subject: Re: OUTER JOIN
Date: Wed, 23 Apr 2003 18:55:36 +0100
User-Agent: KMail/1.4.3
References: <000401c304ec$a77547e0$0301a8c0@amdosoft.local>
In-Reply-To: <000401c304ec$a77547e0$0301a8c0@amdosoft.local>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200304231855.37050.dev@archonet.com>

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 =3D SD.shift_type_id(+) AND SD.app_language_id(+) =3D 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,=20
SD.description
FROM shift_type ST LEFT JOIN shift_desc SD=20
  ON ST.id=3DSD.shift_type_id AND SD.app_language_id=3D2
ORDER BY ST.order_num;

That seems to work. Out of curiosity, what were you trying?

--=20
  Richard Huxton