Skip site navigation (1) Skip section navigation (2)

Re: subselect prob in view

From: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: subselect prob in view
Date: 2004-06-21 15:11:42
Message-ID: 200406211611.42573.gary.stainburn@ringways.co.uk (view raw or flat)
Thread:
Lists: pgsql-sql
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
-- 
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     


In response to

Responses

pgsql-sql by date

Next:From: Jie LiangDate: 2004-06-21 16:28:38
Subject: Re: Prepare Statement
Previous:From: Tom LaneDate: 2004-06-21 14:19:43
Subject: Re: subselect prob in view

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group