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

Hello

I'd probably write the query something like this:

SELECT ST.id, ST.order_num, ST.from_time, ST.to_time, ST.disabled, SD.description FROM shift_type ST LEFT OUTER JOIN
shift_descSD   ON (ST.id = SD.shift_type_id AND SD.app_language_id = 2) ORDER BY ST.order_num
 

I haven't tried the query, so I'm not all sure it works though =)

Regards,
Patrik Kudo

Marco Roda wrote:
> Hallo,
> 
> 
> 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
>