Re: self outer join

On Tue, 6 Nov 2001, David Link wrote:

> Hi,
>
> In pg 7.1 using the new outer join syntax.
>
> SELECT *
> FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);
>
> or
>
> SELECT *
> FROM t1 LEFT OUTER JOIN t2 USING (col);
>
> How does one specify an alias for the table being joined. This is
> important if you are creating an outer join to the same table:
>
> Oracle syntax (simplified) would be:
>
> select r.key,
> r.rank,
> r2.rank as last_weeks_rank
> from rank r,
> rank r2
> where r.key = (+)r2.key and
> r2.week = r1.week - 1
> ;

I think (untested)
select r.key, r.rank, r2.rank as last_weeks_rank
from (rank r left outer join rank r2 using (key))
where r2.week=r1week-1;