X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id B4F60D1B209
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 22 Jun 2004 10:56:29 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 60113-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 22 Jun 2004 13:56:28 +0000 (GMT)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 13FF4D1B267
	for <pgsql-sql@postgresql.org>; Tue, 22 Jun 2004 10:56:23 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i5MDuS24017155;
	Tue, 22 Jun 2004 09:56:28 -0400 (EDT)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: subselect prob in view 
In-reply-to: <200406221034.10689.gary.stainburn@ringways.co.uk> 
References: <200406211110.39940.gary.stainburn@ringways.co.uk>
	<1916.1087827583@sss.pgh.pa.us>
	<200406211611.42573.gary.stainburn@ringways.co.uk>
	<200406221034.10689.gary.stainburn@ringways.co.uk>
Comments: In-reply-to Gary Stainburn <gary.stainburn@ringways.co.uk>
	message dated "Tue, 22 Jun 2004 10:34:10 +0100"
Date: Tue, 22 Jun 2004 09:56:27 -0400
Message-ID: <17154.1087912587@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200406/204
X-Sequence-Number: 18149

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