From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: subselect prob in view
Date: Mon, 21 Jun 2004 16:11:42 +0100
Message-ID: <200406211611.42573.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <1916.1087827583@sss.pgh.pa.us>
References: <200406211110.39940.gary.stainburn@ringways.co.uk>
<1916.1087827583@sss.pgh.pa.us>
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
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: gary.stainburn@ringways.co.uk, tgl@sss.pgh.pa.us
Subject: Re: subselect prob in view
In-Reply-To: <200406211611.42573.gary.stainburn@ringways.co.uk>
* 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