Re: self outer join

Try:

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

"David Link" <dlink@soundscan.com> wrote in message
news:3BE7F948.93E656D3@soundscan.com...
> 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