Thread: INNER JOINS in 7.0.3 & 7.1.3
I have a DB in 7.1.3 that I am trying to downgrade to 7.0.3, and I've run into a little problem with INNER JOINS. This query (just as an example) causes an error : SELECT a.name as hometeam, c.name as homemanager, c.email as homeemail, b.name as awayteam, d.name as awaymanager, d.email as awayemail FROM teams AS a INNER JOIN managers AS c ON (a.managerid = c.managerid), teams AS b INNER JOIN managers as d ON (b.managerid = d.managerid) where a.teamid = 13 AND b.teamid = 15; The error being: ERROR: parser: parse error at or near "," A query with a single inner join works fine. I've checked the changes file for 7.1 on the website, but I can't find INNER JOINS mentioned as being something that changed. Can anyone shed any light on this for me? -- | Stuart Grimshaw <stuart@footballnet.com> | Chief Operations Officer | Football Networks Ltd |- | t:07976 625221 | f:0870 7060260
Stuart Grimshaw <stuart.pgsql@smgsystems.co.uk> writes:
> I have a DB in 7.1.3 that I am trying to downgrade to 7.0.3, and I've run
> into a little problem with INNER JOINS.
The JOIN syntax doesn't really work in 7.0.*.
Why would you want to downgrade anyway?
regards, tom lane
Stuart Grimshaw <stuart.pgsql@smgsystems.co.uk> writes:
> It turns out that the live server we've rented is
> a Cobalt RAQ4 server, which uses Postgresql 7.0.3
That's a step forward; the last I heard, Cobalt was using 6.something.
> I've enquired on the RAQ4 support forums about upgrading Postgres, but
> the server uses it for to keep track of all it's system settings, and
> I have been warned off upgrading by a couple of people.
I wouldn't try it either without a good understanding of what Cobalt
uses it for, and what system services might stop working if one shuts
down the postmaster...
However, the fact that there is a 7.0.* server present on the machine
doesn't mean you have to use 7.0.* too. There's nothing to stop you
from running another postmaster of a more recent vintage in parallel
with the built-in server. (I currently have 7.0.3, 7.1.3, and 7.2devel
servers running on the machine I'm typing this on.) You just need to
build the extra server with a nonstandard install directory and port.
For example, I configure my devel server with
./configure --with-pgport=5440 --prefix=/home/postgres/testversion ...
and then I don't have to think about it. The devel version of psql
(installed in /home/postgres/testversion/bin) automatically defaults
to using port 5440 instead of 5432, so as long as I keep the right
version of psql at the front of my PATH, everything works nicely.
You would need to fix the system boot scripts to start the extra
postmaster as well as the regular one, but I assume you can manage
that.
regards, tom lane
IIRC You can't have multiple join clauses, per se, but you can "chain" joins. I am not sure how that is done with a self join, but since inner joins are implicit why not just: SELECT a.name as hometeam, c.name as homemanager, c.email as homeemail, b.name as awayteam, d.name as awaymanager, d.email as awayemail FROM teams a, managers c, teams b, managers d where a.managerid = c.managerid AND b.managerid = d.managerid AND a.teamid = 13 AND b.teamid = 15; Keary Suska Esoteritech, Inc. "Leveraging Open Source for a better Internet" > From: Stuart Grimshaw <stuart.pgsql@smgsystems.co.uk> > Date: Tue, 30 Oct 2001 15:16:53 +0000 > To: pgsql-general@postgresql.org > Subject: [GENERAL] INNER JOINS in 7.0.3 & 7.1.3 > > I have a DB in 7.1.3 that I am trying to downgrade to 7.0.3, and I've run > into a little problem with INNER JOINS. > > This query (just as an example) causes an error : > > SELECT a.name as hometeam, c.name as homemanager, c.email as homeemail, > b.name as awayteam, d.name as awaymanager, d.email as awayemail FROM teams AS > a INNER JOIN managers AS c ON (a.managerid = c.managerid), teams AS b INNER > JOIN managers as d ON (b.managerid = d.managerid) where a.teamid = 13 AND > b.teamid = 15; > > The error being: > ERROR: parser: parse error at or near "," > > A query with a single inner join works fine. I've checked the changes file > for 7.1 on the website, but I can't find INNER JOINS mentioned as being > something that changed. > > Can anyone shed any light on this for me? > > -- > > | Stuart Grimshaw <stuart@footballnet.com> > | Chief Operations Officer > | Football Networks Ltd > |- > | t:07976 625221 > | f:0870 7060260 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
On Tuesday 30 October 2001 16:23 pm, Keary Suska wrote: > IIRC You can't have multiple join clauses, per se, but you can "chain" > joins. I am not sure how that is done with a self join, but since inner > joins are implicit why not just: > > SELECT a.name as hometeam, c.name as homemanager, c.email as homeemail, > b.name as awayteam, d.name as awaymanager, d.email as awayemail > FROM teams a, managers c, teams b, managers d > where a.managerid = c.managerid AND b.managerid = d.managerid AND a.teamid > = 13 AND b.teamid = 15; That's the way I had it at some point in the dev process, but I switched over to using joins to try and realise some performance increase. -- | Stuart Grimshaw <stuart@footballnet.com> | Chief Operations Officer | Football Networks Ltd |- | t:07976 625221 | f:0870 7060260