Re: self outer join

Try:

SELECT *
FROM Table t1
JOIN Table t2 ON t1.col = t2.col

"David Link" <dlink(at)soundscan(dot)com> wrote in message
news:3BE7F948(dot)93E656D3(at)soundscan(dot)com(dot)(dot)(dot)
> 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
> ;
>
>
> This is quite painful to do using the older outer join technique with
> the UNION ALL ... (unless someone can do this better):
>
> select t.upckey, r.rank, r2.rank as last_weeks_ranking
> from title t,
> rank r,
> rank r2
> where r.upckey = t.upckey and
> r2.upckey = t.upckey and
> r.week = 200102 and
> r2.week = r.week-1 and
> r.media = 'M' and
> r2.media = 'M'
> UNION ALL
> select t.upckey, r.rank, null as last_weeks_ranking
> from title t,
> rank r
> where r.upckey = t.upckey and
> r.week = 200102 and
> r.media = 'M' and
> not exists (select r2.week
> from rank r2
> where r2.upckey = t.upckey and
> r2.week = r.week - 1 and
> r2.media = 'M')
> order by
> r.rank
> ;
>
>
> Phewy. If that's it, then I'll do it programmatically with cursors.
>
> Thanks for any and all help on this
> -David
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org