From: | Don Yury <yura(at)vpcit(dot)ru> |
---|---|
To: | pgsql-sql(at)postgreSQL(dot)org |
Subject: | outer joins |
Date: | 1999-07-27 10:24:25 |
Message-ID: | 379D88D9.AC9F473A@vpcit.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Herouth Maoz | 1999-07-27 14:36:09 | Re: [INTERFACES] How can I work thru this key problem? |
Previous Message | Predrag Lesic | 1999-07-27 09:21:19 | SELECT .. WHERE field IN (SELECT .. ) |