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