Re: subselect prob in view

Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> The two selects work seperately, but I'm still getting the 
> syntax for the combined quiery wrong.

What you've got here reduces to

select co.co_r_id, co.count as com_count, cor.count as com_unseen from (select ...) co, (select ...) cor on co.co_r_id
=cor.co_r_id;
 

which is invalid because "ON something" must be associated with JOIN.
You could write either of

select co.co_r_id, co.count as com_count, cor.count as com_unseen from (select ...) co join (select ...) cor on
co.co_r_id= cor.co_r_id;
 

select co.co_r_id, co.count as com_count, cor.count as com_unseen from (select ...) co, (select ...) cor where
co.co_r_id= cor.co_r_id;
 

but you can't mix-and-match.

With an inner join there isn't any semantic difference between ON and
WHERE, so it's a matter of taste which to use.  But with outer joins
there's a big difference.
        regards, tom lane