Re: left joins

On 7/6/05, Ragnar Hafstað <gnari(at)simnet(dot)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=1
> > where h.tn > 20
> > and h.tn < 30
>
> really ? is this legal SQL ?
> is this a 8.0 feature ?
> I get syntax error at or near "and" at character 41
>

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=p.r and h.pos=r.pos and p.r_order=1)
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.html

Tony Wasson