Thread: self outer join

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

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;

Same way as you would alias any table in your FROM
clause, i.e.:
FROM table AS t1 LEFT OUTER JOIN table AS t2
     ON t1.field = t2.field
The AS keyword is optional here, but I think preferred
for clarity.

--- David Link <dlink@soundscan.com> wrote:

> 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:


__________________________________________________
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com

David Link writes:

> How does one specify an alias for the table being joined.

select * from t1 as a left outer join t1 as b using (a.col1 = b.col2);

--
Peter Eisentraut   peter_e@gmx.net

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