Re: subselect prob in view

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(dot)stainburn(at)ringways(dot)co(dot)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.
>
> Gary

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_id, co_r_id, cor_viewed
goole-# from comments c, co_recipients co
goole-# where c.co_id = co.cor_co_id;
co_id | co_r_id | cor_viewed
-------+---------+-------------------------------
1 | 1 | 2004-06-22 10:15:52.945065+01
1 | 1 | 2004-06-22 10:15:52.952895+01
2 | 1 |
2 | 1 |
3 | 2 |
(5 rows)

goole=# select co_r_id, count(co_r_id) from comments group by co_r_id;
co_r_id | count
---------+-------
1 | 2
2 | 1
(2 rows)

goole=# select co_r_id, count(co_r_id) 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;
co_r_id | count
---------+-------
1 | 1
2 | 1
(2 rows)

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;
ERROR: parser: parse error at or near "on"
goole=#

--
Gary Stainburn

This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000