self outer join - Mailing list pgsql-general

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