Forum OpenACS Development: Outer join fun

Collapse
1: Outer join fun
Posted by Ola Hansson on 10/05/01 05:16 PM
I'm porting mp3jukebox. How should this view be ported? 
Oracle:

create or replace view mp3_mp3_playlist_map_view as
	select m.mp3_id,		
	       m.playlist_id,
	       m.sort_key,
	       nvl(v.total,0) as total
	from   mp3_mp3_playlist_map m,
	       (select mp3_id,
	               playlist_id,
	               sum(vote) as total
	        from   mp3_votes
                group by mp3_id, playlist_id) v
	where  m.mp3_id = v.mp3_id (+)
	and    m.playlist_id = v.playlist_id (+);

My attempt in Posrgres:

create view mp3_mp3_playlist_map_view as
	select m.mp3_id,		
	       m.playlist_id,
	       m.sort_key,
	       coalesce(v.total,0) as total
	from   mp3_mp3_playlist_map m
	       left join (select mp3_id,
	               playlist_id,
	               sum(vote) as total
		       from   mp3_votes
		       group by mp3_id, playlist_id) v
		       using (mp3_id)
	       left join v using (playlist_id);
I get: ERROR: Relation 'v' does not exist
Collapse
2: Response to Outer join fun (response to 1)
Posted by Don Baccus on 10/05/01 05:30 PM
In the original query, "v" is the name given to the subselect that's the right operand of your first outer join. You don't need two outer joins, here. The Oracle form is joining on two keys is all, it's just one join. So something like: 
create view mp3_mp3_playlist_map_view as
  select m.mp3_id,
         m.playlist_id,
         ..etc...
  from mp3_mp3_playlist_map m
       left join (select ...your subselect ...)
       using (mp3_id, playlist_id);
should be sufficient.
Collapse
3: Response to Outer join fun (response to 1)
Posted by Gilbert Wong on 10/05/01 05:31 PM

Try this: 

from   mp3_mp3_playlist_map m
	       left join (select mp3_id,
	               playlist_id,
	               sum(vote) as total
		       from   mp3_votes
		       group by mp3_id, playlist_id) v
		       using (mp3_id, playlist_id);

I'm not sure if it's a "," or an "and" between mp3_id and playlist_id. One of them should work.

Collapse
4: Response to Outer join fun (response to 1)
Posted by Ola Hansson on 10/05/01 05:42 PM
thanks!

(the "v" after the subselect is needed, BTW)