DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=gFFzrBYL9eSOwltkNgTdioEKxmY7XCrXRAw6Tf0FTrI4pPDJqjRK+xa2fscJBzSx/84guSnVNs97ekMF08YgX87OIFVvrmg7HC6lg6r02qPERgqU2ZC1cqtrS1VCpd1UYrOJAjej2GrJj9AOnuPKDW9SrRPs6KfDxLQm4jLpo5c=
Message-ID: <6d8daee305070618117ab12541@mail.gmail.com>
Date: Wed, 6 Jul 2005 18:11:32 -0700
From: Tony Wasson <ajwasson@gmail.com>
Reply-To: Tony Wasson <ajwasson@gmail.com>
To: =?ISO-8859-1?Q?Ragnar_Hafsta=F0?= <gnari@simnet.is>
Subject: Re: left joins
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1120695557.14290.22.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <20050706103303.3C2C424E7A4@smtp.nildram.co.uk>
	<1120695557.14290.22.camel@localhost.localdomain>

On 7/6/05, Ragnar Hafsta=F0 <gnari@simnet.is> wrote:
> On Wed, 2005-07-06 at 11:33 +0100, Nick Stone wrote:
> > I've had exactly yhe same problem - try changing the query to.
> >
> > select count(*)
> >  from  h left join p using (r,pos) and p.r_order=3D1
> > where h.tn > 20
> > and h.tn < 30
>=20
> really ? is this legal SQL ?
> is this a 8.0 feature ?
> I get syntax error at or near "and" at character 41
>=20

SQL like this works for me when I write it with an ON statement
instead of a USING.

select count(*)
 from  h left join p on (h.r=3Dp.r and h.pos=3Dr.pos and p.r_order=3D1)
 where h.tn > 20
 and h.tn < 30

Filtering within the join condition is very useful when doing a left outer =
join.

Here's another example "from the book" doing this type of filter
within the join:
http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.ht=
ml

Tony Wasson