pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsubselect prob in view
6+ messages / 3 participants
[nested] [flat]
* subselect prob in view
@ 2004-06-21 10:10 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 6+ messages in thread
From: Gary Stainburn @ 2004-06-21 10:10 UTC (permalink / raw)
To: pgsql-sql
Hi folks,
I've got the view:
create view nrequest_details as
select r.r_id, r_registration, r.r_chassis, r.r_vehicle,
r.r_fuel,r.r_pack_mats,
r.r_delivery, r_delivery::date-now()::date as r_remaining,
r.r_created, r.r_completed,
d.d_des, de.de_des,
u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target,
t.t_id, t.t_des,
s.s_id, s.s_des,
c.c_id, c.c_des
from requests r, users u, request_types t,
request_states s, dealerships d, departments de, customers c
where r_d_id = d.d_id and
r_s_id = s.s_id and
r_c_id = c.c_id and
r_t_id = t.t_id and
r_d_id = d.d_id and
r_de_id = de.de_id and
r_u_id = u.u_id;
to which I want to add a count (2 eventually), so that it becomes:
create view nrequest_details as
select r.r_id, r_registration, r.r_chassis, r.r_vehicle,
r.r_fuel,r.r_pack_mats,
r.r_delivery, r_delivery::date-now()::date as r_remaining,
r.r_created, r.r_completed,
d.d_des, de.de_des,
u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target,
t.t_id, t.t_des,
s.s_id, s.s_des,
c.c_id, c.c_des,
co.count as comments
-- cor.count as comments_unseen
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
-- left outer join (select co_r_id, count(co_r_id) from comments where
cor_viewed is null
-- group by co_r_id) co on
-- co.co_r_id = r.r_id
where r_d_id = d.d_id and
r_s_id = s.s_id and
r_c_id = c.c_id and
r_t_id = t.t_id and
r_d_id = d.d_id and
r_de_id = de.de_id and
r_u_id = u.u_id;
but I get the error:
[gary@eddie gary]$ psql -f goole1.sql
DROP
psql:goole1.sql:45: ERROR: Relation "r" does not exist
[gary@eddie gary]$
I tried using the table name instead of the alias but instead got the error:
[gary@eddie gary]$ psql -f goole1.sql
psql:goole1.sql:1: ERROR: view "nrequest_details" does not exist
psql:goole1.sql:45: NOTICE: Adding missing FROM-clause entry for table
"requests"
psql:goole1.sql:45: ERROR: JOIN/ON clause refers to "requests", which is not
part of JOIN
[gary@eddie gary]$
which at least makes sense.
--
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: subselect prob in view
@ 2004-06-21 14:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2004-06-21 14:19 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: subselect prob in view
@ 2004-06-21 15:11 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Gary Stainburn @ 2004-06-21 15:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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.
Gary
--
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: subselect prob in view
@ 2004-06-22 09:34 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 2 replies; 6+ messages in thread
From: Gary Stainburn @ 2004-06-22 09:34 UTC (permalink / raw)
To: pgsql-sql
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.
>
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: subselect prob in view
@ 2004-06-22 13:31 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2004-06-22 13:31 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: subselect prob in view
@ 2004-06-22 13:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2004-06-22 13:56 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> The two selects work seperately, but I'm still getting the
> syntax for the combined quiery wrong.
What you've got here reduces to
select co.co_r_id, co.count as com_count, cor.count as com_unseen
from
(select ...) co,
(select ...) cor on co.co_r_id = cor.co_r_id;
which is invalid because "ON something" must be associated with JOIN.
You could write either of
select co.co_r_id, co.count as com_count, cor.count as com_unseen
from
(select ...) co join
(select ...) cor on co.co_r_id = cor.co_r_id;
select co.co_r_id, co.count as com_count, cor.count as com_unseen
from
(select ...) co,
(select ...) cor
where co.co_r_id = cor.co_r_id;
but you can't mix-and-match.
With an inner join there isn't any semantic difference between ON and
WHERE, so it's a matter of taste which to use. But with outer joins
there's a big difference.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-06-22 13:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-21 10:10 subselect prob in view Gary Stainburn <gary.stainburn@ringways.co.uk>
2004-06-21 14:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-21 15:11 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2004-06-22 09:34 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2004-06-22 13:31 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-06-22 13:56 ` Tom Lane <tgl@sss.pgh.pa.us>
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