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
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...
Show quoted text
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?
