Re: subselect prob in view

Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)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