pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOuter Joins
38+ messages / 23 participants
[nested] [flat]
* Outer Joins
@ 1999-02-10 21:32 Jan Mönnich <jm15@irz301.inf.tu-dresden.de>
0 siblings, 0 replies; 38+ messages in thread
From: Jan Mönnich @ 1999-02-10 21:32 UTC (permalink / raw)
To: pgsql-sql
Hi!
How can I make an select with an outer Join (Access: "..from a left join b
on a.i=b.i" or
Sybase ("..where a.i *= b.i..")?
Thanks
Jan
^ permalink raw reply [nested|flat] 38+ messages in thread
* [SQL] OUTER JOINS
@ 1999-04-15 18:21 Dan Janowski <danj@3skel.com>
0 siblings, 0 replies; 38+ messages in thread
From: Dan Janowski @ 1999-04-15 18:21 UTC (permalink / raw)
To: pgsql-sql
I've looked through the docs and mail lists but I am coming
up short on references for LEFT/RIGHT/FULL OUTER JOIN
avalability
or functional workarounds. It seems that PostgreSQL does
not have it.
Any recommendations on how to aproximate the functionality?
As a curiosity, is there a problem with supporting these
OUTER joins or is it merely on the list?
Many thanks,
Dan
--
Dan Janowski danj@3skel.com Triskelion Systems,
Inc. Bronx, NY
^ permalink raw reply [nested|flat] 38+ messages in thread
* [SQL] OUTER JOINS
@ 1999-04-15 20:11 Dan Janowski <danj@3skel.com>
0 siblings, 1 reply; 38+ messages in thread
From: Dan Janowski @ 1999-04-15 20:11 UTC (permalink / raw)
To: pgsql-sql
I've looked through the docs and mail lists but I am coming
up short on references for LEFT/RIGHT/FULL OUTER JOIN
avalability
or functional workarounds. It seems that PostgreSQL does
not have it.
Any recommendations on how to aproximate the functionality?
As a curiosity, is there a problem with supporting these
OUTER joins or is it merely on the list?
Many thanks,
Dan
--
Dan Janowski danj@3skel.com Triskelion Systems,
Inc. Bronx, NY
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [SQL] OUTER JOINS
@ 1999-04-19 06:26 Joe Shevland <J_Shevland@TurnAround.com.au>
parent: Dan Janowski <danj@3skel.com>
0 siblings, 2 replies; 38+ messages in thread
From: Joe Shevland @ 1999-04-19 06:26 UTC (permalink / raw)
To: Dan Janowski <danj@3skel.com>; +Cc: pgsql-sql
I didn't see a response for this, so here goes... I believe you can
implement joins using the followng syntax:
select t1.value from table_one t1, table_two t2 where t1.key = t2.key;
So this would be a straight LEFT join in my understanding. Now, I'm
venturing beyond my experience with PostgreSQL here, but you may be able
to use the associated SQL syntaxes:
select ... where t1.key *= t2.key
select ... where t1.key =* t2.key
etc...
I hope I'm right here :)
Regards,
Joe.
Dan Janowski wrote:
>
> I've looked through the docs and mail lists but I am coming
> up short on references for LEFT/RIGHT/FULL OUTER JOIN
> avalability
> or functional workarounds. It seems that PostgreSQL does
> not have it.
>
> Any recommendations on how to aproximate the functionality?
>
> As a curiosity, is there a problem with supporting these
> OUTER joins or is it merely on the list?
>
> Many thanks,
>
> Dan
>
> --
> Dan Janowski danj@3skel.com Triskelion Systems,
> Inc. Bronx, NY
--
---------------------------------------------
,-._|\ | Joe Shevland
/ \ | Principal Consultant
\_,--._/ | Turnaround Solutions Pty. Ltd.
v | http://www.TurnAround.com.au
---------------------------------------------
Skate to where the puck is going and not to
where it has been - Wayne Gretzky
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [SQL] OUTER JOINS
@ 1999-04-19 13:26 Dan Janowski <danj@3skel.com>
parent: Joe Shevland <J_Shevland@TurnAround.com.au>
1 sibling, 0 replies; 38+ messages in thread
From: Dan Janowski @ 1999-04-19 13:26 UTC (permalink / raw)
To: J_Shevland@TurnAround.com.au; +Cc: pgsql-sql
Thanks for giving it a stab. Looking at the available
operators,
there is no =* or *= ops available. Although I am curious
where it
comes from.
Dan
Joe Shevland wrote:
> ...
> select t1.value from table_one t1, table_two t2 where t1.key = t2.key;
>
> So this would be a straight LEFT join in my understanding. Now, I'm
> venturing beyond my experience with PostgreSQL here, but you may be able
> to use the associated SQL syntaxes:
>
> select ... where t1.key *= t2.key
> select ... where t1.key =* t2.key
> ...
>
> Dan Janowski wrote:
> >
> > I've looked through the docs and mail lists but I am coming
> > up short on references for LEFT/RIGHT/FULL OUTER JOIN
> > avalability
> > or functional workarounds. It seems that PostgreSQL does
> > not have it.
> >
> > Any recommendations on how to aproximate the functionality?
> >
> > As a curiosity, is there a problem with supporting these
> > OUTER joins or is it merely on the list?
> >
--
Dan Janowski danj@3skel.com Triskelion Systems,
Inc. Bronx, NY
^ permalink raw reply [nested|flat] 38+ messages in thread
* RE: [SQL] OUTER JOINS
@ 1999-04-19 15:16 Michael J Davis <michael.j.davis@tvguide.com>
0 siblings, 0 replies; 38+ messages in thread
From: Michael J Davis @ 1999-04-19 15:16 UTC (permalink / raw)
To: pgsql-sql
As far as I have been able to determine, outer joins are not supported in
PostgreSQL.
> -----Original Message-----
> From: Joe Shevland [SMTP:J_Shevland@TurnAround.com.au]
> Sent: Monday, April 19, 1999 12:27 AM
> To: Dan Janowski
> Cc: pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] OUTER JOINS
>
> I didn't see a response for this, so here goes... I believe you can
> implement joins using the followng syntax:
>
> select t1.value from table_one t1, table_two t2 where t1.key = t2.key;
>
> So this would be a straight LEFT join in my understanding. Now, I'm
> venturing beyond my experience with PostgreSQL here, but you may be able
> to use the associated SQL syntaxes:
>
> select ... where t1.key *= t2.key
> select ... where t1.key =* t2.key
>
> etc...
>
> I hope I'm right here :)
>
> Regards,
> Joe.
>
> Dan Janowski wrote:
> >
> > I've looked through the docs and mail lists but I am coming
> > up short on references for LEFT/RIGHT/FULL OUTER JOIN
> > avalability
> > or functional workarounds. It seems that PostgreSQL does
> > not have it.
> >
> > Any recommendations on how to aproximate the functionality?
> >
> > As a curiosity, is there a problem with supporting these
> > OUTER joins or is it merely on the list?
> >
> > Many thanks,
> >
> > Dan
> >
> > --
> > Dan Janowski danj@3skel.com Triskelion Systems,
> > Inc. Bronx, NY
>
> --
> ---------------------------------------------
> ,-._|\ | Joe Shevland
> / \ | Principal Consultant
> \_,--._/ | Turnaround Solutions Pty. Ltd.
> v | http://www.TurnAround.com.au
> ---------------------------------------------
> Skate to where the puck is going and not to
> where it has been - Wayne Gretzky
^ permalink raw reply [nested|flat] 38+ messages in thread
* RE: [SQL] OUTER JOINS
@ 1999-04-19 15:18 Michael J Davis <michael.j.davis@tvguide.com>
0 siblings, 0 replies; 38+ messages in thread
From: Michael J Davis @ 1999-04-19 15:18 UTC (permalink / raw)
To: pgsql-sql
*= and =* are Oracle syntax for outer joins.
> -----Original Message-----
> From: Dan Janowski [SMTP:danj@3skel.com]
> Sent: Monday, April 19, 1999 7:27 AM
> To: J_Shevland@TurnAround.com.au
> Cc: pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] OUTER JOINS
>
> Thanks for giving it a stab. Looking at the available
> operators,
> there is no =* or *= ops available. Although I am curious
> where it
> comes from.
>
> Dan
>
>
> Joe Shevland wrote:
> > ...
> > select t1.value from table_one t1, table_two t2 where t1.key = t2.key;
> >
> > So this would be a straight LEFT join in my understanding. Now, I'm
> > venturing beyond my experience with PostgreSQL here, but you may be able
> > to use the associated SQL syntaxes:
> >
> > select ... where t1.key *= t2.key
> > select ... where t1.key =* t2.key
> > ...
> >
> > Dan Janowski wrote:
> > >
> > > I've looked through the docs and mail lists but I am coming
> > > up short on references for LEFT/RIGHT/FULL OUTER JOIN
> > > avalability
> > > or functional workarounds. It seems that PostgreSQL does
> > > not have it.
> > >
> > > Any recommendations on how to aproximate the functionality?
> > >
> > > As a curiosity, is there a problem with supporting these
> > > OUTER joins or is it merely on the list?
> > >
>
> --
> Dan Janowski danj@3skel.com Triskelion Systems,
> Inc. Bronx, NY
^ permalink raw reply [nested|flat] 38+ messages in thread
* FW: [SQL] OUTER JOINS
@ 1999-04-20 02:10 Michael J Davis <michael.j.davis@tvguide.com>
0 siblings, 0 replies; 38+ messages in thread
From: Michael J Davis @ 1999-04-20 02:10 UTC (permalink / raw)
To: pgsql-sql
*= and =* are Oracle syntax for outer joins.
-----Original Message-----
From: Dan Janowski [SMTP:danj@3skel.com]
<mailto:[SMTP:danj@3skel.com]>
Sent: Monday, April 19, 1999 7:27 AM
To: J_Shevland@TurnAround.com.au
<mailto:J_Shevland@TurnAround.com.au>
Cc: pgsql-sql@postgreSQL.org <mailto:pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] OUTER JOINS
Thanks for giving it a stab. Looking at the available
operators,
there is no =* or *= ops available. Although I am curious
where it
comes from.
Dan
Joe Shevland wrote:
> ...
> select t1.value from table_one t1, table_two t2 where t1.key =
t2.key;
>
> So this would be a straight LEFT join in my understanding. Now,
I'm
> venturing beyond my experience with PostgreSQL here, but you may
be able
> to use the associated SQL syntaxes:
>
> select ... where t1.key *= t2.key
> select ... where t1.key =* t2.key
> ...
>
> Dan Janowski wrote:
> >
> > I've looked through the docs and mail lists but I am coming
> > up short on references for LEFT/RIGHT/FULL OUTER JOIN
> > avalability
> > or functional workarounds. It seems that PostgreSQL does
> > not have it.
> >
> > Any recommendations on how to aproximate the functionality?
> >
> > As a curiosity, is there a problem with supporting these
> > OUTER joins or is it merely on the list?
> >
--
Dan Janowski danj@3skel.com <mailto:danj@3skel.com>
Triskelion Systems,
Inc. Bronx, NY
^ permalink raw reply [nested|flat] 38+ messages in thread
* RE:[SQL] OUTER JOINS
@ 1999-04-20 02:10 Michael J Davis <michael.j.davis@tvguide.com>
0 siblings, 0 replies; 38+ messages in thread
From: Michael J Davis @ 1999-04-20 02:10 UTC (permalink / raw)
To: pgsql-sql
As far as I have been able to determine, outer joins are not supported in
PostgreSQL.
-----Original Message-----
From: Joe Shevland [SMTP:J_Shevland@TurnAround.com.au]
<mailto:[SMTP:J_Shevland@TurnAround.com.au]>
Sent: Monday, April 19, 1999 12:27 AM
To: Dan Janowski
Cc: pgsql-sql@postgreSQL.org <mailto:pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] OUTER JOINS
I didn't see a response for this, so here goes... I believe you can
implement joins using the followng syntax:
select t1.value from table_one t1, table_two t2 where t1.key =
t2.key;
So this would be a straight LEFT join in my understanding. Now, I'm
venturing beyond my experience with PostgreSQL here, but you may be
able
to use the associated SQL syntaxes:
select ... where t1.key *= t2.key
select ... where t1.key =* t2.key
etc...
I hope I'm right here :)
Regards,
Joe.
Dan Janowski wrote:
>
> I've looked through the docs and mail lists but I am coming
> up short on references for LEFT/RIGHT/FULL OUTER JOIN
> avalability
> or functional workarounds. It seems that PostgreSQL does
> not have it.
>
> Any recommendations on how to aproximate the functionality?
>
> As a curiosity, is there a problem with supporting these
> OUTER joins or is it merely on the list?
>
> Many thanks,
>
> Dan
>
> --
> Dan Janowski danj@3skel.com <mailto:danj@3skel.com>
Triskelion Systems,
> Inc. Bronx, NY
--
---------------------------------------------
,-._|\ | Joe Shevland
/ \ | Principal Consultant
\_,--._/ | Turnaround Solutions Pty. Ltd.
v | http://www.TurnAround.com.au
<http://www.TurnAround.com.au;
---------------------------------------------
Skate to where the puck is going and not to
where it has been - Wayne Gretzky
^ permalink raw reply [nested|flat] 38+ messages in thread
* RE: [SQL] OUTER JOINS
@ 1999-04-20 02:34 Michael J Davis <michael.j.davis@tvguide.com>
0 siblings, 0 replies; 38+ messages in thread
From: Michael J Davis @ 1999-04-20 02:34 UTC (permalink / raw)
To: pgsql-sql
As far as I have been able to determine, outer joins are not
supported in PostgreSQL.
-----Original Message-----
From: Joe Shevland [SMTP:J_Shevland@TurnAround.com.au]
Sent: Monday, April 19, 1999 12:27 AM
To: Dan Janowski
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] OUTER JOINS
I didn't see a response for this, so here goes... I believe
you can
implement joins using the followng syntax:
select t1.value from table_one t1, table_two t2 where t1.key
= t2.key;
So this would be a straight LEFT join in my understanding.
Now, I'm
venturing beyond my experience with PostgreSQL here, but you
may be able
to use the associated SQL syntaxes:
select ... where t1.key *= t2.key
select ... where t1.key =* t2.key
etc...
I hope I'm right here :)
Regards,
Joe.
Dan Janowski wrote:
>
> I've looked through the docs and mail lists but I am
coming
> up short on references for LEFT/RIGHT/FULL OUTER JOIN
> avalability
> or functional workarounds. It seems that PostgreSQL does
> not have it.
>
> Any recommendations on how to aproximate the
functionality?
>
> As a curiosity, is there a problem with supporting these
> OUTER joins or is it merely on the list?
>
> Many thanks,
>
> Dan
>
> --
> Dan Janowski danj@3skel.com Triskelion Systems,
> Inc. Bronx, NY
--
---------------------------------------------
,-._|\ | Joe Shevland
/ \ | Principal Consultant
\_,--._/ | Turnaround Solutions Pty. Ltd.
v | http://www.TurnAround.com.au
---------------------------------------------
Skate to where the puck is going and not to
where it has been - Wayne Gretzky
^ permalink raw reply [nested|flat] 38+ messages in thread
* RE: [SQL] OUTER JOINS
@ 1999-04-20 02:34 Michael J Davis <michael.j.davis@tvguide.com>
0 siblings, 1 reply; 38+ messages in thread
From: Michael J Davis @ 1999-04-20 02:34 UTC (permalink / raw)
To: pgsql-sql
*= and =* are Oracle syntax for outer joins.
-----Original Message-----
From: Dan Janowski [SMTP:danj@3skel.com]
Sent: Monday, April 19, 1999 7:27 AM
To: J_Shevland@TurnAround.com.au
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] OUTER JOINS
Thanks for giving it a stab. Looking at the available
operators,
there is no =* or *= ops available. Although I am curious
where it
comes from.
Dan
Joe Shevland wrote:
> ...
> select t1.value from table_one t1, table_two t2 where
t1.key = t2.key;
>
> So this would be a straight LEFT join in my understanding.
Now, I'm
> venturing beyond my experience with PostgreSQL here, but
you may be able
> to use the associated SQL syntaxes:
>
> select ... where t1.key *= t2.key
> select ... where t1.key =* t2.key
> ...
>
> Dan Janowski wrote:
> >
> > I've looked through the docs and mail lists but I am
coming
> > up short on references for LEFT/RIGHT/FULL OUTER JOIN
> > avalability
> > or functional workarounds. It seems that PostgreSQL does
> > not have it.
> >
> > Any recommendations on how to aproximate the
functionality?
> >
> > As a curiosity, is there a problem with supporting these
> > OUTER joins or is it merely on the list?
> >
--
Dan Janowski danj@3skel.com Triskelion Systems,
Inc. Bronx, NY
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [SQL] OUTER JOINS
@ 1999-04-20 03:33 Joe Shevland <J_Shevland@TurnAround.com.au>
parent: Michael J Davis <michael.j.davis@tvguide.com>
0 siblings, 0 replies; 38+ messages in thread
From: Joe Shevland @ 1999-04-20 03:33 UTC (permalink / raw)
To: Michael J Davis <michael.j.davis@tvguide.com>; +Cc: pgsql-sql
Aha, I was wondering where they were dredged from :) Is 6.5 going to
support OJ's?
Michael J Davis wrote:
>
> *= and =* are Oracle syntax for outer joins.
>
> -----Original Message-----
> From: Dan Janowski [SMTP:danj@3skel.com]
> Sent: Monday, April 19, 1999 7:27 AM
> To: J_Shevland@TurnAround.com.au
> Cc: pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] OUTER JOINS
>
> Thanks for giving it a stab. Looking at the available
> operators,
> there is no =* or *= ops available. Although I am curious
> where it
> comes from.
>
> Dan
>
> Joe Shevland wrote:
> > ...
> > select t1.value from table_one t1, table_two t2 where
> t1.key = t2.key;
> >
> > So this would be a straight LEFT join in my understanding.
> Now, I'm
> > venturing beyond my experience with PostgreSQL here, but
> you may be able
> > to use the associated SQL syntaxes:
> >
> > select ... where t1.key *= t2.key
> > select ... where t1.key =* t2.key
> > ...
> >
> > Dan Janowski wrote:
> > >
> > > I've looked through the docs and mail lists but I am
> coming
> > > up short on references for LEFT/RIGHT/FULL OUTER JOIN
> > > avalability
> > > or functional workarounds. It seems that PostgreSQL does
> > > not have it.
> > >
> > > Any recommendations on how to aproximate the
> functionality?
> > >
> > > As a curiosity, is there a problem with supporting these
> > > OUTER joins or is it merely on the list?
> > >
>
> --
> Dan Janowski danj@3skel.com Triskelion Systems,
> Inc. Bronx, NY
--
---------------------------------------------
,-._|\ | Joe Shevland
/ \ | Principal Consultant
\_,--._/ | Turnaround Solutions Pty. Ltd.
v | http://www.TurnAround.com.au
---------------------------------------------
Skate to where the puck is going and not to
where it has been - Wayne Gretzky
^ permalink raw reply [nested|flat] 38+ messages in thread
* RE: [SQL] OUTER JOINS
@ 1999-04-20 05:30 Michael J Davis <michael.j.davis@tvguide.com>
0 siblings, 0 replies; 38+ messages in thread
From: Michael J Davis @ 1999-04-20 05:30 UTC (permalink / raw)
To: 'J_Shevland@TurnAround.com.au' <J_Shevland@TurnAround.com.au>; +Cc: pgsql-sql
I don't think outer joins are planned for 6.5. Possibly 6.6?
-----Original Message-----
From: Joe Shevland [SMTP:J_Shevland@TurnAround.com.au]
Sent: Monday, April 19, 1999 9:33 PM
To: Michael J Davis
Cc: 'pgsql-sql@postgreSQL.org'
Subject: Re: [SQL] OUTER JOINS
Aha, I was wondering where they were dredged from :) Is 6.5 going to
support OJ's?
Michael J Davis wrote:
>
> *= and =* are Oracle syntax for outer joins.
>
> -----Original Message-----
> From: Dan Janowski [SMTP:danj@3skel.com]
> Sent: Monday, April 19, 1999 7:27 AM
> To: J_Shevland@TurnAround.com.au
> Cc: pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] OUTER JOINS
>
> Thanks for giving it a stab. Looking at the
available
> operators,
> there is no =* or *= ops available. Although I am
curious
> where it
> comes from.
>
> Dan
>
> Joe Shevland wrote:
> > ...
> > select t1.value from table_one t1, table_two t2
where
> t1.key = t2.key;
> >
> > So this would be a straight LEFT join in my
understanding.
> Now, I'm
> > venturing beyond my experience with PostgreSQL
here, but
> you may be able
> > to use the associated SQL syntaxes:
> >
> > select ... where t1.key *= t2.key
> > select ... where t1.key =* t2.key
> > ...
> >
> > Dan Janowski wrote:
> > >
> > > I've looked through the docs and mail lists
but I am
> coming
> > > up short on references for LEFT/RIGHT/FULL
OUTER JOIN
> > > avalability
> > > or functional workarounds. It seems that
PostgreSQL does
> > > not have it.
> > >
> > > Any recommendations on how to aproximate the
> functionality?
> > >
> > > As a curiosity, is there a problem with
supporting these
> > > OUTER joins or is it merely on the list?
> > >
>
> --
> Dan Janowski danj@3skel.com Triskelion
Systems,
> Inc. Bronx, NY
--
---------------------------------------------
,-._|\ | Joe Shevland
/ \ | Principal Consultant
\_,--._/ | Turnaround Solutions Pty. Ltd.
v | http://www.TurnAround.com.au
---------------------------------------------
Skate to where the puck is going and not to
where it has been - Wayne Gretzky
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [SQL] OUTER JOINS
@ 1999-04-28 15:58 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Joe Shevland <J_Shevland@TurnAround.com.au>
1 sibling, 0 replies; 38+ messages in thread
From: Herouth Maoz @ 1999-04-28 15:58 UTC (permalink / raw)
To: Dan Janowski <danj@3skel.com>; J_Shevland@TurnAround.com.au, M Simms <grim@argh.demon.co.uk>; +Cc: pgsql-sql
At 16:26 +0300 on 19/04/1999, Dan Janowski wrote:
> Thanks for giving it a stab. Looking at the available
> operators,
> there is no =* or *= ops available. Although I am curious
> where it
> comes from.
To the best of my knowledge, outer joins are not yet supported in
PostgreSQL. As far as I recall, you can achieve the same effect with a
union.
For example, if you have a people table and a pets table, where each
person's pet is joined to the owner by the owner's id colum, you'll
theoretically do something like:
SELECT surname, firstname, pet_name
FROM people, pets
WHERE id *= owner_id
ORDER BY surname, firstname;
So instead, you'll have to do something like:
SELECT surname, firstname, pet_name
FROM people, pets
WHERE id = owner_id
UNION
SELECT surname, firstname, null
FROM people
WHERE not exist (
SELECT 1
FROM pets
WHERE owner_id = id
)
ORDER BY surname, firstname;
Excuse me if I goofed something syntactically, I don't have time to
actually create the tables and test the above. Note, however, that a UNION
removes duplicates.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 38+ messages in thread
* outer joins
@ 1999-07-27 10:24 Don Yury <yura@vpcit.ru>
0 siblings, 0 replies; 38+ messages in thread
From: Don Yury @ 1999-07-27 10:24 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 38+ messages in thread
* Outer Joins
@ 2000-02-19 19:53 Charles Mott <cmott@scientech.com>
0 siblings, 1 reply; 38+ messages in thread
From: Charles Mott @ 2000-02-19 19:53 UTC (permalink / raw)
To: pgsql-sql
How are outer joins implemented within PostgreSQL?
I would like to do a query of the form:
select a.x, b.y from a, b where a.x *= b.x;
The *= operator does not seem to exist. I've been
able to devise some clumsy workarounds by creating
temporary tables:
create table temp_table as
select a.x, b.y from a, b where a.x = b.x;
insert into temp_table
select x from a
except
select a.x from a, b where a.x = b.x;
Outer joins are apparently quite common, but I haven't
been able to find anything on this in either the users
guide, the draft of Bruce Momjian's upcoming book or the
Deja News archives.
Charles Mott
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [SQL] Outer Joins
@ 2000-02-19 20:14 Jens Glaser <pge99brq@studserv.uni-leipzig.de>
parent: Charles Mott <cmott@scientech.com>
0 siblings, 0 replies; 38+ messages in thread
From: Jens Glaser @ 2000-02-19 20:14 UTC (permalink / raw)
To: Charles Mott <cmott@scientech.com>; +Cc: pgsql-sql
Hi,
On Sat, 19 Feb 2000, Charles Mott wrote:
> How are outer joins implemented within PostgreSQL?
Sadly enough, they are not :-(
--
Jens Glaser Phil.-Rosenth.-Str. 31-438 04103 Leipzig (0341) 2157167
^ permalink raw reply [nested|flat] 38+ messages in thread
* Outer-joins
@ 2000-04-04 08:03 Gerhard Dieringer <DieringG@eba-haus.de>
0 siblings, 1 reply; 38+ messages in thread
From: Gerhard Dieringer @ 2000-04-04 08:03 UTC (permalink / raw)
To: pgsql-sql
A short question for the postgreSQL gurus:
Does the new version 7.0 support outer-joins?
If the answer is no :-((, are there any plans to implement it in a future version??
Thank you
Gerhard
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Outer-joins
@ 2000-04-04 10:34 Peter Eisentraut <e99re41@DoCS.UU.SE>
parent: Gerhard Dieringer <DieringG@eba-haus.de>
0 siblings, 0 replies; 38+ messages in thread
From: Peter Eisentraut @ 2000-04-04 10:34 UTC (permalink / raw)
To: Gerhard Dieringer <DieringG@eba-haus.de>; +Cc: pgsql-sql
On Tue, 4 Apr 2000, Gerhard Dieringer wrote:
> Does the new version 7.0 support outer-joins?
No.
> If the answer is no :-((, are there any plans to implement it in a
> future version??
Yes. Seems like 7.1.
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
^ permalink raw reply [nested|flat] 38+ messages in thread
* Outer joins
@ 2000-04-18 17:15 Michael S. Kelly <michaelk@axian.com>
0 siblings, 1 reply; 38+ messages in thread
From: Michael S. Kelly @ 2000-04-18 17:15 UTC (permalink / raw)
To: pgsql-sql
I understand that outer joins are not supported in PostgreSQL. I'm just
starting my first PostgreSQL project, and have to admit that this concerns
me. Outer joins have come in handy, at some point, in almost every database
project I've ever worked on. I guess I'd like to hear how you all work
around this issue.
Thanks,
-=michael=-
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Outer joins
@ 2000-04-18 17:54 kaiq@realtyideas.com
parent: Michael S. Kelly <michaelk@axian.com>
0 siblings, 2 replies; 38+ messages in thread
From: kaiq@realtyideas.com @ 2000-04-18 17:54 UTC (permalink / raw)
To: Michael S. Kelly <michaelk@axian.com>; +Cc: pgsql-sql
why it is a problem at all, if you use sql so long? outer join
can be easily simulated.
On Tue, 18 Apr 2000, Michael S. Kelly wrote:
> I understand that outer joins are not supported in PostgreSQL. I'm just
> starting my first PostgreSQL project, and have to admit that this concerns
> me. Outer joins have come in handy, at some point, in almost every database
> project I've ever worked on. I guess I'd like to hear how you all work
> around this issue.
>
> Thanks,
>
> -=michael=-
>
^ permalink raw reply [nested|flat] 38+ messages in thread
* RE: Outer joins
@ 2000-04-18 18:17 Michael S. Kelly <michaelk@axian.com>
parent: kaiq@realtyideas.com
1 sibling, 1 reply; 38+ messages in thread
From: Michael S. Kelly @ 2000-04-18 18:17 UTC (permalink / raw)
To: kaiq@realtyideas.com; +Cc: pgsql-sql
Actually found the answer to my question in Bruce Momjian's book. I'm
shamefaced to say that I never considered UNION ALL solve the problem.
thanks,
-=michael=-
*****************************************************
* Michael S. Kelly
* 4800 SW Griffith Dr., Ste. 202
* Beaverton, OR 97005 USA
* voice: (503)644-6106 x122 fax: (503)643-8425
* <michaelk@axian.com>
* http://www.axian.com/
*****************************************************
* Axian: Software Consulting and Training
*****************************************************
-----Original Message-----
From: kaiq@realtyideas.com [mailto:kaiq@realtyideas.com]
Sent: Tuesday, April 18, 2000 10:54 AM
To: Michael S. Kelly
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Outer joins
why it is a problem at all, if you use sql so long? outer join
can be easily simulated.
On Tue, 18 Apr 2000, Michael S. Kelly wrote:
> I understand that outer joins are not supported in PostgreSQL. I'm just
> starting my first PostgreSQL project, and have to admit that this concerns
> me. Outer joins have come in handy, at some point, in almost every
database
> project I've ever worked on. I guess I'd like to hear how you all work
> around this issue.
>
> Thanks,
>
> -=michael=-
>
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Outer joins
@ 2000-04-18 18:29 Bruce Momjian <pgman@candle.pha.pa.us>
parent: kaiq@realtyideas.com
1 sibling, 0 replies; 38+ messages in thread
From: Bruce Momjian @ 2000-04-18 18:29 UTC (permalink / raw)
To: kaiq@realtyideas.com; +Cc: Michael S. Kelly <michaelk@axian.com>; pgsql-sql
There is an FAQ item about it.
> why it is a problem at all, if you use sql so long? outer join
> can be easily simulated.
>
> On Tue, 18 Apr 2000, Michael S. Kelly wrote:
>
> > I understand that outer joins are not supported in PostgreSQL. I'm just
> > starting my first PostgreSQL project, and have to admit that this concerns
> > me. Outer joins have come in handy, at some point, in almost every database
> > project I've ever worked on. I guess I'd like to hear how you all work
> > around this issue.
> >
> > Thanks,
> >
> > -=michael=-
> >
>
>
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Outer joins
@ 2000-04-19 00:26 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Michael S. Kelly <michaelk@axian.com>
0 siblings, 0 replies; 38+ messages in thread
From: Bruce Momjian @ 2000-04-19 00:26 UTC (permalink / raw)
To: Michael S. Kelly <michaelk@axian.com>; +Cc: kaiq@realtyideas.com; pgsql-sql
[Charset iso-8859-1 unsupported, filtering to ASCII...]
> Actually found the answer to my question in Bruce Momjian's book. I'm
> shamefaced to say that I never considered UNION ALL solve the problem.
Don't be. It took me a few years of apologizing for no Outer joins
until I found the answer. If others already knew it, I hadn't seen it.
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 38+ messages in thread
* Outer Joins
@ 2000-11-01 08:02 Marc Rohloff <Marc.Rohloff@eskom.co.za>
0 siblings, 2 replies; 38+ messages in thread
From: Marc Rohloff @ 2000-11-01 08:02 UTC (permalink / raw)
To: pgsql-sql
I've been looking at the open-source databases for a project I am working on and while reading about Postgres I saw that they do not support outer joins yet. I was intrigued by their solution of using a union query.
Something Like:
select a.col1, b.col2 from a,b where a.col1 = b.col2
union
select a.col1, NULL from a where a.col1 not in (select b.col2 from b)
But I was wondering if the following would work (it does in some other databases)
select a.col1, b.col2 from a,b
where a.col1 = b.col2
or b.col2 is null
or maybe even
select a.col1, b.col2 from a,b
where a.col1 = b.col2
or a.col1 not in (select b.col2 from b)
These would seem to be far more efficient than a union query
(I would try this but I don't have a Unix box at the moment to install PostgreSQL on!)
Marc Rohloff
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Outer Joins
@ 2000-11-01 11:56 Grant Finnemore <gaf@ucs.co.za>
parent: Marc Rohloff <Marc.Rohloff@eskom.co.za>
1 sibling, 0 replies; 38+ messages in thread
From: Grant Finnemore @ 2000-11-01 11:56 UTC (permalink / raw)
To: Marc Rohloff <Marc.Rohloff@eskom.co.za>; +Cc: pgsql-sql
Marc,
I did not look at your queries, but outer joins are supported in the latest development version of PostgreSQL, and will be supported in the upcoming 7.1 release of the software - beta due soon.
Regards,
Grant
Marc Rohloff wrote:
> I've been looking at the open-source databases for a project I am working on and while reading about Postgres I saw that they do not support outer joins yet. I was intrigued by their solution of using a union query.
>
> Something Like:
> select a.col1, b.col2 from a,b where a.col1 = b.col2
> union
> select a.col1, NULL from a where a.col1 not in (select b.col2 from b)
>
> But I was wondering if the following would work (it does in some other databases)
>
> select a.col1, b.col2 from a,b
> where a.col1 = b.col2
> or b.col2 is null
>
> or maybe even
>
> select a.col1, b.col2 from a,b
> where a.col1 = b.col2
> or a.col1 not in (select b.col2 from b)
>
> These would seem to be far more efficient than a union query
> (I would try this but I don't have a Unix box at the moment to install PostgreSQL on!)
>
> Marc Rohloff
--
> Poorly planned software requires a genius to write it
> and a hero to use it.
Grant Finnemore BSc(Eng) (mailto:gaf@ucs.co.za)
Software Engineer Universal Computer Services
Tel (+27)(11)712-1366 PO Box 31266 Braamfontein 2017, South Africa
Cell (+27)(82)604-5536 20th Floor, 209 Smit St., Braamfontein
Fax (+27)(11)339-3421 Johannesburg, South Africa
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Outer Joins
@ 2000-11-01 17:01 Josh Berkus <josh@agliodbs.com>
parent: Marc Rohloff <Marc.Rohloff@eskom.co.za>
1 sibling, 0 replies; 38+ messages in thread
From: Josh Berkus @ 2000-11-01 17:01 UTC (permalink / raw)
To: pgsql-sql
Marc,
> select a.col1, b.col2 from a,b
> where a.col1 = b.col2
> or b.col2 is null
The above would select each record in a that matched a record in b, plus
each record in b for every possible a where b.col2 was null - not where
there was no match.
> select a.col1, b.col2 from a,b
> where a.col1 = b.col2
> or a.col1 not in (select b.col2 from b)
This would work, but it would be *much* slower than a UNION query. "Not
In" queries are perhaps the slowest you can run; see the earlier thread
"Query Problem" for a discussion. UNION queries are, in fact, very fast
... just awkward to code and manipulate.
-Josh
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Outer Joins
@ 2000-11-02 07:11 Marc Rohloff <Marc.Rohloff@eskom.co.za>
0 siblings, 0 replies; 38+ messages in thread
From: Marc Rohloff @ 2000-11-02 07:11 UTC (permalink / raw)
To: josh@agliodbs.com; pgsql-sql
>> select a.col1, b.col2 from a,b
>> where a.col1 = b.col2
>> or a.col1 not in (select b.col2 from b)
>This would work, but it would be *much* slower than a UNION query. "Not
>In" queries are perhaps the slowest you can run; see the earlier thread
>"Query Problem" for a discussion. UNION queries are, in fact, very fast
>... just awkward to code and manipulate.
Why should this be slower since the UNION Query still has an identical not in clause?
This is far easier (for me) to read.
Marc
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Outer Joins
@ 2000-11-02 16:37 Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 38+ messages in thread
From: Josh Berkus @ 2000-11-02 16:37 UTC (permalink / raw)
To: pgsql-sql
Marc,
> >This would work, but it would be *much* slower than a UNION query. "Not
> >In" queries are perhaps the slowest you can run; see the earlier thread
> >"Query Problem" for a discussion. UNION queries are, in fact, very fast
> >... just awkward to code and manipulate.
>
> Why should this be slower since the UNION Query still has an identical not in clause?
> This is far easier (for me) to read.
Good point. Frankly, if you have a relevant large population of data
(>10,000 rows) to test, I'd love to see comparative execution tests
between the two query structures.
Fortunately, this will all soon become moot; Tom says that outer joins
have been stable in the 7.1 build for a while. Speaking of which,
when's the 7.1 "release"? Huh, huh?
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Outer Joins
@ 2000-11-02 16:55 Josh Berkus <josh@agliodbs.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 38+ messages in thread
From: Josh Berkus @ 2000-11-02 16:55 UTC (permalink / raw)
To: pgsql-sql
Marc, Tom,
> Good point. Frankly, if you have a relevant large population of data
> (>10,000 rows) to test, I'd love to see comparative execution tests
> between the two query structures.
>
> Fortunately, this will all soon become moot; Tom says that outer joins
> have been stable in the 7.1 build for a while. Speaking of which,
> when's the 7.1 "release"? Huh, huh?
On second thought, couldn't we use some kind of EXCLUDES clause to
expedite this? Tom?
Further, it occurs to me that as in my query, you don't want to use "NOT
IN" on *either* version. Instead, use "NOT EXISTS", which is much, much
faster.
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
^ permalink raw reply [nested|flat] 38+ messages in thread
* outer joins
@ 2001-04-04 11:03 Algirdas ©akmanas <asakmanas@grafton.lt>
0 siblings, 2 replies; 38+ messages in thread
From: Algirdas ©akmanas @ 2001-04-04 11:03 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I'm new to postgre, I've changed my work and consequently now i'm moving
from MS plaform.
In MS SQL there are such constructs left or right outer join, in postgres
there are no such thing
Can You offer me strategy to make query that selects from table (a) and
joins to it another (b)
on e.g. a.id=b.aid but joins so that in case there is no value in table b
NULL is left in result:
in MS SQL i've used query:
select a.id, b.name from a left outer join b on a.id=b.aid
table a table b
id | aid | name
----------- ----------------
1 1 | Tom
2 3 | Sam
result:
a.id | b.name
---------------------
1 | Tom
2 | NULL
thank you in advance
Algirdas ©akmanas
IT manager
+370 99 90369
asakmanas@grafton.lt
Grafton Entertainment
http://www.tvnet.lt
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: outer joins
@ 2001-04-04 14:22 tjk@tksoft.com <tjk@tksoft.com>
parent: Algirdas ©akmanas <asakmanas@grafton.lt>
1 sibling, 0 replies; 38+ messages in thread
From: tjk@tksoft.com @ 2001-04-04 14:22 UTC (permalink / raw)
To: asakmanas@grafton.lt; +Cc: pgsql-sql
Algirdas,
This should do the trick:
SELECT a.id,b.name FROM a,b WHERE a.id=b.id UNION SELECT id,null FROM a WHERE id NOT IN (SELECT id FROM b);
Troy
>
> Hi all,
>
> I'm new to postgre, I've changed my work and consequently now i'm moving
> from MS plaform.
> In MS SQL there are such constructs left or right outer join, in postgres
> there are no such thing
>
> Can You offer me strategy to make query that selects from table (a) and
> joins to it another (b)
> on e.g. a.id=b.aid but joins so that in case there is no value in table b
> NULL is left in result:
> in MS SQL i've used query:
>
> select a.id, b.name from a left outer join b on a.id=b.aid
>
> table a table b
>
> id | aid | name
> ----------- ----------------
> 1 1 | Tom
> 2 3 | Sam
>
> result:
> a.id | b.name
> ---------------------
> 1 | Tom
> 2 | NULL
>
> thank you in advance
>
> Algirdas ©akmanas
> IT manager
> +370 99 90369
> asakmanas@grafton.lt
> Grafton Entertainment
> http://www.tvnet.lt
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: outer joins
@ 2001-04-04 14:49 Poet/Joshua Drake <poet@linuxports.com>
parent: Algirdas ©akmanas <asakmanas@grafton.lt>
1 sibling, 0 replies; 38+ messages in thread
From: Poet/Joshua Drake @ 2001-04-04 14:49 UTC (permalink / raw)
To: Algirdas ©akmanas <asakmanas@grafton.lt>; +Cc: pgsql-sql
Hello,
I believe these are supported in 7.1
On Wed, 4 Apr 2001, [iso-8859-4] Algirdas ©akmanas wrote:
>Hi all,
>
>I'm new to postgre, I've changed my work and consequently now i'm moving
>from MS plaform.
>In MS SQL there are such constructs left or right outer join, in postgres
>there are no such thing
>
>Can You offer me strategy to make query that selects from table (a) and
>joins to it another (b)
>on e.g. a.id=b.aid but joins so that in case there is no value in table b
>NULL is left in result:
>in MS SQL i've used query:
>
>select a.id, b.name from a left outer join b on a.id=b.aid
>
>table a table b
>
>id | aid | name
>----------- ----------------
>1 1 | Tom
>2 3 | Sam
>
>result:
>a.id | b.name
>---------------------
>1 | Tom
>2 | NULL
>
>thank you in advance
>
>Algirdas ©akmanas
>IT manager
>+370 99 90369
>asakmanas@grafton.lt
>Grafton Entertainment
>http://www.tvnet.lt
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
--
<COMPANY>CommandPrompt - http://www.commandprompt.com </COMPANY>
<PROJECT>OpenDocs, LLC. - http://www.opendocs.org </PROJECT>
<PROJECT>LinuxPorts - http://www.linuxports.com </PROJECT>
<WEBMASTER>LDP - http://www.linuxdoc.org </WEBMASTER>
--
Instead of asking why a piece of software is using "1970s technology,"
start asking why software is ignoring 30 years of accumulated wisdom.
--
^ permalink raw reply [nested|flat] 38+ messages in thread
* RE: outer joins
@ 2001-04-04 15:01 Picard, Cyril <cyril.picard@eads-dsn.com>
0 siblings, 0 replies; 38+ messages in thread
From: Picard, Cyril @ 2001-04-04 15:01 UTC (permalink / raw)
To: 'Algirdas Sakmanas' <asakmanas@grafton.lt>; +Cc: pgsql-sql
I've read that the version 7.1 provides outer join feature. Since I did not
try it yet, I've no more information about it.
> -----Message d'origine-----
> De: Algirdas Sakmanas [SMTP:asakmanas@grafton.lt]
> Date: mercredi 4 avril 2001 13:03
> À: pgsql-sql@postgresql.org
> Objet: [SQL] outer joins
>
> Hi all,
>
> I'm new to postgre, I've changed my work and consequently now i'm moving
> from MS plaform.
> In MS SQL there are such constructs left or right outer join, in postgres
> there are no such thing
>
> Can You offer me strategy to make query that selects from table (a) and
> joins to it another (b)
> on e.g. a.id=b.aid but joins so that in case there is no value in table b
> NULL is left in result:
> in MS SQL i've used query:
>
> select a.id, b.name from a left outer join b on a.id=b.aid
>
> table a table b
>
> id | aid | name
> ----------- ----------------
> 1 1 | Tom
> 2 3 | Sam
>
> result:
> a.id | b.name
> ---------------------
> 1 | Tom
> 2 | NULL
>
> thank you in advance
>
> Algirdas Sakmanas
> IT manager
> +370 99 90369
> asakmanas@grafton.lt
> Grafton Entertainment
> http://www.tvnet.lt
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 38+ messages in thread
* Outer joins?
@ 2006-04-28 13:23 Emils <gnudiff@gmail.com>
0 siblings, 2 replies; 38+ messages in thread
From: Emils @ 2006-04-28 13:23 UTC (permalink / raw)
To: pgsql-sql
Hello!
I am a longtime postgres user (started around 10 years ago), however,
as for some years I've been using it mostly as administrator.
Now that I have started a project and doing some SQL, I've come up
something I don't believe is right. Maybe I am too rusty on my SQL -
if so, please forgive me, but I checked it and my reasoning seemed ok
to me.
I am trying to do simple self-joins.
The table structure is:
object_values
==========
obj_id
att_id
value
namely, each object can have arbitrary number of attributes each of
them with a value.
What I want, is a simple table of objects with some of their specific
attributes, the result should be in form:
obj_id1 o1att1_value o1att2_value o1att3_value
obj_id2 o2att1_value o2att2_value o2att3_value
...
Obviously, if eg obj2 doesn't have att2 in the table, I want a NULL in
that grid point.
So, I thought some nested outer joins should be OK?
SELECT
OV.obj_id AS obj_id,
OV.value AS NAME,
ov1.value AS DESCRIPTION,
ov2.value AS ICON
FROM
object_values OV LEFT JOIN object_values ov1 USING(obj_id)
LEFT JOIN object_values ov2 USING(obj_id)
WHERE OV.att_id=7 AND ov1.att_id=8 AND ov2.att_id=16;
So, I figured this should get me all objects that have atttribute 7
defined, regardless of whether the other attributes exist for them?
However, for some reason PG8.1 is giving me something like an INNER
join on this query - namely ONLY rows where ALL the attributes ARE
present.
Am I doing something wrong? As I said my SQL is rusty, but this looked
pretty straightforward to me...
Thanks in advance,
Emils
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Outer joins?
@ 2006-04-28 13:46 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Emils <gnudiff@gmail.com>
1 sibling, 0 replies; 38+ messages in thread
From: Stephan Szabo @ 2006-04-28 13:46 UTC (permalink / raw)
To: emils.klotins@gmail.com; +Cc: pgsql-sql
On Fri, 28 Apr 2006, Emils wrote:
> I am trying to do simple self-joins.
>
> The table structure is:
>
> object_values
> ==========
> obj_id
> att_id
> value
>
> namely, each object can have arbitrary number of attributes each of
> them with a value.
>
> What I want, is a simple table of objects with some of their specific
> attributes, the result should be in form:
>
> obj_id1 o1att1_value o1att2_value o1att3_value
> obj_id2 o2att1_value o2att2_value o2att3_value
> ...
>
> Obviously, if eg obj2 doesn't have att2 in the table, I want a NULL in
> that grid point.
>
> So, I thought some nested outer joins should be OK?
>
> SELECT
> OV.obj_id AS obj_id,
> OV.value AS NAME,
> ov1.value AS DESCRIPTION,
> ov2.value AS ICON
> FROM
> object_values OV LEFT JOIN object_values ov1 USING(obj_id)
> LEFT JOIN object_values ov2 USING(obj_id)
> WHERE OV.att_id=7 AND ov1.att_id=8 AND ov2.att_id=16;
AFAIK, effectively first the join happens then the where filter. So,
imagine the output of the joins without any where clause and then apply
the where clause as a filter upon that. Even if you got NULL extended
rows, you'd filter them out because the ov1.att_id and ov2.att_id tests
would filter them out. In addition, you won't actually get NULL extended
rows I think, because there will always be at least one row with matching
obj_id (the one from ov that's being worked on).
I think putting a test in an ON clause associated with the join (using
something like ... left join object_values ov1 ON(ov.obj_id = ov1.obj_id
and ov1.att_id=8) rather than where will consider both as part of the join
and null extend even if there are obj_id matches if none of those have
att_id=8.
Another way of doing the same thing is using subselects in from to filter
the right hand tables you wish to join.
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Outer joins?
@ 2006-04-28 15:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Emils <gnudiff@gmail.com>
1 sibling, 0 replies; 38+ messages in thread
From: Tom Lane @ 2006-04-28 15:31 UTC (permalink / raw)
To: emils.klotins@gmail.com; +Cc: pgsql-sql
Emils <gnudiff@gmail.com> writes:
> The table structure is:
> object_values
> ==========
> obj_id
> att_id
> value
> namely, each object can have arbitrary number of attributes each of
> them with a value.
> What I want, is a simple table of objects with some of their specific
> attributes, the result should be in form:
> obj_id1 o1att1_value o1att2_value o1att3_value
> obj_id2 o2att1_value o2att2_value o2att3_value
> ...
This isn't an outer-join problem, it's a crosstab problem. Try the
crosstab functions in contrib/tablefunc.
regards, tom lane
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Outer joins
@ 2006-04-29 08:33 Emils <gnudiff@gmail.com>
0 siblings, 0 replies; 38+ messages in thread
From: Emils @ 2006-04-29 08:33 UTC (permalink / raw)
To: pgsql-sql
(damn gmail, sorry about priv mail earlier)
> Stephan Szabo <sszabo@megazone.bigpanda.com>:
> I think putting a test in an ON clause associated with the join (using
> something like ... left join object_values ov1 ON(ov.obj_id = ov1.obj_id
> and ov1.att_id=8) rather than where will consider both as part of the join
> and null extend even if there are obj_id matches if none of those have
> att_id=8.
Thanks, that worked!
2006/4/28, Tom Lane <tgl@sss.pgh.pa.us>:
> Emils <gnudiff@gmail.com> writes:
> > The table structure is:
>
> > object_values
> > ==========
> This isn't an outer-join problem, it's a crosstab problem. Try the
> crosstab functions in contrib/tablefunc.
>
Thanks, I looked into it, unfortunately I might have to have also a
solution for where installing contrib stuff is not possible.
Emils
^ permalink raw reply [nested|flat] 38+ messages in thread
end of thread, other threads:[~2006-04-29 08:33 UTC | newest]
Thread overview: 38+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-02-10 21:32 Outer Joins Jan Mönnich <jm15@irz301.inf.tu-dresden.de>
1999-04-15 18:21 [SQL] OUTER JOINS Dan Janowski <danj@3skel.com>
1999-04-15 20:11 [SQL] OUTER JOINS Dan Janowski <danj@3skel.com>
1999-04-19 06:26 ` Re: [SQL] OUTER JOINS Joe Shevland <J_Shevland@TurnAround.com.au>
1999-04-19 13:26 ` Re: [SQL] OUTER JOINS Dan Janowski <danj@3skel.com>
1999-04-28 15:58 ` Re: [SQL] OUTER JOINS Herouth Maoz <herouth@oumail.openu.ac.il>
1999-04-19 15:16 RE: [SQL] OUTER JOINS Michael J Davis <michael.j.davis@tvguide.com>
1999-04-19 15:18 RE: [SQL] OUTER JOINS Michael J Davis <michael.j.davis@tvguide.com>
1999-04-20 02:10 FW: [SQL] OUTER JOINS Michael J Davis <michael.j.davis@tvguide.com>
1999-04-20 02:10 RE:[SQL] OUTER JOINS Michael J Davis <michael.j.davis@tvguide.com>
1999-04-20 02:34 RE: [SQL] OUTER JOINS Michael J Davis <michael.j.davis@tvguide.com>
1999-04-20 02:34 RE: [SQL] OUTER JOINS Michael J Davis <michael.j.davis@tvguide.com>
1999-04-20 03:33 ` Re: [SQL] OUTER JOINS Joe Shevland <J_Shevland@TurnAround.com.au>
1999-04-20 05:30 RE: [SQL] OUTER JOINS Michael J Davis <michael.j.davis@tvguide.com>
1999-07-27 10:24 outer joins Don Yury <yura@vpcit.ru>
2000-02-19 19:53 Outer Joins Charles Mott <cmott@scientech.com>
2000-02-19 20:14 ` Jens Glaser <pge99brq@studserv.uni-leipzig.de>
2000-04-04 08:03 Outer-joins Gerhard Dieringer <DieringG@eba-haus.de>
2000-04-04 10:34 ` Re: Outer-joins Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-04-18 17:15 Outer joins Michael S. Kelly <michaelk@axian.com>
2000-04-18 17:54 ` Re: Outer joins kaiq@realtyideas.com
2000-04-18 18:17 ` RE: Outer joins Michael S. Kelly <michaelk@axian.com>
2000-04-19 00:26 ` Re: Outer joins Bruce Momjian <pgman@candle.pha.pa.us>
2000-04-18 18:29 ` Re: Outer joins Bruce Momjian <pgman@candle.pha.pa.us>
2000-11-01 08:02 Outer Joins Marc Rohloff <Marc.Rohloff@eskom.co.za>
2000-11-01 11:56 ` Grant Finnemore <gaf@ucs.co.za>
2000-11-01 17:01 ` Josh Berkus <josh@agliodbs.com>
2000-11-02 07:11 Re: Outer Joins Marc Rohloff <Marc.Rohloff@eskom.co.za>
2000-11-02 16:37 Re: Outer Joins Josh Berkus <josh@agliodbs.com>
2000-11-02 16:55 ` Josh Berkus <josh@agliodbs.com>
2001-04-04 11:03 outer joins Algirdas ©akmanas <asakmanas@grafton.lt>
2001-04-04 14:22 ` Re: outer joins tjk@tksoft.com <tjk@tksoft.com>
2001-04-04 14:49 ` Re: outer joins Poet/Joshua Drake <poet@linuxports.com>
2001-04-04 15:01 RE: outer joins Picard, Cyril <cyril.picard@eads-dsn.com>
2006-04-28 13:23 Outer joins? Emils <gnudiff@gmail.com>
2006-04-28 13:46 ` Re: Outer joins? Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-04-28 15:31 ` Re: Outer joins? Tom Lane <tgl@sss.pgh.pa.us>
2006-04-29 08:33 Re: Outer joins Emils <gnudiff@gmail.com>
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