Thread: [SQL] OUTER JOINS
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
Hi, I am using postgresql 6.5 snapshot. I can not update. Could anyone tell me what's wrong, please? update ibs_br_all_total_units tr set first_name= (select first_name from ibs_subscriber tnwhere tr.pin = tn.pin ); "/tmp/psql.60000.13342" 4 lines, 117 characters ERROR: parser: syntax error at or near "tr" Thanks. Regards, Chai
Chairudin Sentosa ha scritto: > Hi, > > I am using postgresql 6.5 snapshot. > I can not update. > Could anyone tell me what's wrong, please? > > update ibs_br_all_total_units tr set first_name= > (select first_name from ibs_subscriber tn > where tr.pin = tn.pin > ); > > "/tmp/psql.60000.13342" 4 lines, 117 characters > ERROR: parser: syntax error at or near "tr" > AFAIK PostgreSQL doesn't allow subselects on UPDATE. José
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
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
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
*= 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
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 AMTo: Dan JanowskiCc: 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 canimplement 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'mventuring beyond my experience with PostgreSQL here, butyou may be ableto use the associated SQL syntaxes: select ... where t1.key *= t2.keyselect ... 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/FULLOUTER JOIN> avalability> or functional workarounds. It seems that PostgreSQL does> not have it.> > Any recommendationson how to aproximate the functionality?> > As a curiosity, is there a problem with supporting these> OUTERjoins 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\_,--._/ | TurnaroundSolutions Pty. Ltd. v | http://www.TurnAround.com.au <http://www.TurnAround.com.au> ---------------------------------------------Skate to where the puck is going and not towhereit has been - Wayne Gretzky
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
seemsthat 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
thelist? > > 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 -
WayneGretzky
*= 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
Iam 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 ...
wheret1.key =* t2.key > ... > > Dan Janowski wrote: > > > > I've looked through the docs and mail lists
butI 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
merelyon the list? > >
-- Dan Janowski danj@3skel.com Triskelion Systems, Inc. Bronx, NY
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
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
PMTo: Michael J DavisCc: 'pgsql-sql@postgreSQL.org'Subject: Re: [SQL] OUTER JOINS
Aha, I was wondering where they were dredged from :) Is 6.5 going tosupport 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:27AM> 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
andmail 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
toaproximate 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\_,--._/ |
TurnaroundSolutions Pty. Ltd. v | http://www.TurnAround.com.au---------------------------------------------Skate
towhere the puck is going and not towhere it has been - Wayne Gretzky
José Soares wrote: > Chairudin Sentosa ha scritto: > > > Hi, > > > > I am using postgresql 6.5 snapshot. > > I can not update. > > Could anyone tell me what's wrong, please? > > > > update ibs_br_all_total_units tr set first_name= > > (select first_name from ibs_subscriber tn > > where tr.pin = tn.pin > > ); > > > > "/tmp/psql.60000.13342" 4 lines, 117 characters > > ERROR: parser: syntax error at or near "tr" > > > > AFAIK PostgreSQL doesn't allow subselects on UPDATE. > > José Hi Jose, How should I do it in PostgreSQL? Thanks Regards, Chai
Try this function: create function a(int,text) returns text as 'begin update ibs_br_all_total_units set first_name = $2 where pin = $1; RETURN $2;end; ' language 'plpgsql'; create table ibs_subscriber( pin int, first_name text); insert into ibs_subscriber values(1,'pippo'); insert into ibs_subscriber values(3,'pluto'); create table ibs_br_all_total_units( pin int, first_name text); insert into ibs_br_all_total_units values(1,'text'); insert into ibs_br_all_total_units values(2,'text'); insert into ibs_br_all_total_units values(3,'text'); select * from ibs_subscriber ; pin|first_name ---+---------- 1|pippo 3|pluto (2 rows) select * from ibs_br_all_total_units ; pin|first_name ---+---------- 1|text 2|text 3|text (3 rows) select a(pin,first_name) from ibs_subscriber ; a ----- pippo pluto (2 rows) select * from ibs_br_all_total_units ; pin|first_name ---+---------- 2|text 1|pippo 3|pluto (3 rows) José Chairudin Sentosa ha scritto: > José Soares wrote: > > > Chairudin Sentosa ha scritto: > > > > > Hi, > > > > > > I am using postgresql 6.5 snapshot. > > > I can not update. > > > Could anyone tell me what's wrong, please? > > > > > > update ibs_br_all_total_units tr set first_name= > > > (select first_name from ibs_subscriber tn > > > where tr.pin = tn.pin > > > ); > > > > > > "/tmp/psql.60000.13342" 4 lines, 117 characters > > > ERROR: parser: syntax error at or near "tr" > > > > > > > AFAIK PostgreSQL doesn't allow subselects on UPDATE. > > > > José > > Hi Jose, > > How should I do it in PostgreSQL? > > Thanks > > Regards, > Chai
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 peopleWHERE 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