From: Don Yury <yura@vpcit.ru>
To: pgsql-sql@postgreSQL.org
Subject: outer joins
Date: Tue, 27 Jul 1999 16:24:25 +0600
Message-ID: <379D88D9.AC9F473A@vpcit.ru> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: yura@vpcit.ru
Subject: Re: outer joins
In-Reply-To: <379D88D9.AC9F473A@vpcit.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox