From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: subselect prob in view
Date: Tue, 22 Jun 2004 06:31:22 -0700 (PDT)
Message-ID: <20040622062603.U13933@megazone.bigpanda.com> (raw)
In-Reply-To: <200406221034.10689.gary.stainburn@ringways.co.uk>
References: <200406211110.39940.gary.stainburn@ringways.co.uk>
<1916.1087827583@sss.pgh.pa.us>
<200406211611.42573.gary.stainburn@ringways.co.uk>
<200406221034.10689.gary.stainburn@ringways.co.uk>
On Tue, 22 Jun 2004, Gary Stainburn wrote:
> On Monday 21 Jun 2004 4:11 pm, Gary Stainburn wrote:
> > On Monday 21 Jun 2004 3:19 pm, Tom Lane wrote:
> > > Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> > > > from requests r, users u, request_types t,
> > > > request_states s, dealerships d, departments de, customers c
> > > > left outer join (select co_r_id, count(co_r_id) from comments
> > > > group by co_r_id) co on
> > > > co.co_r_id = r.r_id
> > > > psql:goole1.sql:45: ERROR: Relation "r" does not exist
> > >
> > > I think you have learned some bad habits from MySQL :-(
> > >
> > > PostgreSQL follows the SQL spec and makes JOIN bind more tightly than
> > > comma. Therefore, in the above the LEFT JOIN is only joining "c" to
> > > "co" and its JOIN ON clause can only reference those two relations.
> > >
> > > You could get the behavior you seem to expect by changing each comma
> > > in the from-list to CROSS JOIN. Then the JOINs all bind left-to-right
> > > and so "r" will be part of the left argument of the LEFT JOIN.
> > >
> > > Note that if you are using a pre-7.4 release this could have negative
> > > effects on performance --- see the user's guide concerning how explicit
> > > JOIN syntax constrains the planner.
> > >
> > > regards, tom lane
> >
> > Thanks for this Tom, but I've never used MySQL.
> >
> > I'll look at the docs and have another go.
> >
> In order to simplify things, I'm just concentrating on the view to give me the
> two tallies. The two selects work seperately, but I'm still getting the
> syntax for the combined quiery wrong. I'm asuming that the problem's before
> the 'on' clause and not the clause itself (I've also tried using 'using'
> instead but that didn't work either.
>
> goole=# select co.co_r_id, co.count as com_count, cor.count as com_unseen
> goole-# from
> goole-# (select co_r_id, count(co_r_id)
> goole(# from comments group by co_r_id) co,
> goole-# (select co_r_id, count(co_r_id)
> goole(# from comments where co_id in
> goole(# (select distinct co_id
> goole(# from comments c, co_recipients co
> goole(# where c.co_id = co.cor_co_id and co.cor_viewed is null)
> goole(# group by co_r_id) cor on co.co_r_id = cor.co_r_id;
AFAICS, you're not using the join syntax between co and cor at the outer
level. There's nothing to attach that on clause to. Either you want that
in a where clause (like in the subselect above it) or you want to use
"inner join" between co and cor.
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: sszabo@megazone.bigpanda.com, gary.stainburn@ringways.co.uk
Subject: Re: subselect prob in view
In-Reply-To: <20040622062603.U13933@megazone.bigpanda.com>
* 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