Thread: OUTER JOIN

Hallo,

I need to port some SQL from Oracle to PostgreSQL v7.2, but I am finding
problems with outer joins.

Here are the tables:
select * from shift_typeid | order_num | from_time | to_time  | disabled
----+-----------+-----------+----------+---------- 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
 

select * from shift_descapp_language_id | shift_type_id | description
-----------------+---------------+-------------              1 |             1 | Morning              1 |             2
|Afternoon              1 |             3 | Night              2 |             6 | SHIFT_DE              1 |
6 | SHIFT_EN
 

and here is the SQL for Oracle:
SELECT ST.id, ST.order_num, ST.from_time, ST.to_time, ST.disabled,
SD.descriptionFROM shift_type ST, shift_desc SDWHERE ST.id = SD.shift_type_id(+) AND SD.app_language_id(+) = 2ORDER 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?

Thank you for your attention,
Marco Roda

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