Message-ID: <379D88D9.AC9F473A@vpcit.ru>
Date: Tue, 27 Jul 1999 16:24:25 +0600
From: Don Yury <yura@vpcit.ru>
MIME-Version: 1.0
To: pgsql-sql@postgreSQL.org
Subject: outer joins
Content-Type: text/plain; charset=koi8-r
Content-Transfer-Encoding: 7bit

Hi All.

I would like to know how people emulates right/left outer joins in
postgres?

Namely, I would like to get list of tables from postgres with parent
class for every table if one exists and null if parent class doesn't
exists, e.g.

TABLE   | PARENT
-------------------
table1  |
table2  |
table3  | table1
table4  | table2
.............

I tried 

select distinct a.relname, c.relname
from pg_class a, pg_inherits b, pg_class c
where a.relkind='r' and a.relname !~~ 'pg_%' and
(b.inhrel=a.oid or
 not exists (select inhrel from pg_inherits where inhrel=a.oid))
and
c.oid=b.inhparent;                                                         


but I get each table wich hasn't parent more than one time.

Following query works pretty well:

select a.relname, c.relname
from pg_class a, pg_inherits b, pg_class c
where a.relkind='r' and a.relname !~~ 'pg_%' and
b.inhrel=a.oid and c.oid=b.inhparent
union
select a.relname, ''
from pg_class a
where a.relkind='r' and a.relname !~~ 'pg_%' and
not exists (select inhrel from pg_inherits where inhrel=a.oid);

but since it's union I can't order result on my desire.

Perhaps anybody knows more simple way to do this?
And generally which methods exists for outer joins substitution?

Sincerely yours, Yury.
don.web-page.net, ICQ 11831432