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