Skip site navigation (1) Skip section navigation (2)

OUTER JOIN

From: "Marco Roda" <MarcoRoda(at)amdosoft(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: OUTER JOIN
Date: 2003-04-17 14:21:36
Message-ID: 000401c304ec$a77547e0$0301a8c0@amdosoft.local (view raw or flat)
Thread:
Lists: pgsql-sql
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_type
 id | 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_desc
 app_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.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?

Thank you for your attention,
Marco Roda


Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2003-04-17 14:26:43
Subject: Re: planner question..
Previous:From: Tom LaneDate: 2003-04-17 14:17:31
Subject: Re: analyse question..

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group