mysql - Using case when count() is zero? -

i'm trying list of articles sorted number of comments. i've made similar process list sorted aticle's votes don't know why don't work.

select a.*,cc.params catparams,cc.title cattitle,u.username, case when char_length(a.alias) concat_ws(":", a.id, a.alias) else a.id end slug, case when char_length(cc.alias) concat_ws(":", cc.id, cc.alias) else cc.id end   catslug, case when (count(rs.id)=0) 0 else count(rs.id) end total_comments jos_content inner join table_users u on u.id = a.created_by inner join table_categories cc on cc.id = a.catid left join table__comments rs on rs.id = a.id 

this code return results if article has @ least 1 entry in comments table; want list of articles , if there no entry in comments table return 0 total_comments. i've tried inner join, left join , right join.

any appreciated!

i think need sub select

select      a.*,     cc.params catparams,     cc.title cattitle,     u.username,     case          when char_length(a.alias)          concat_ws(":", a.id, a.alias)          else a.id end slug,     case      when char_length(cc.alias)          concat_ws(":", cc.id, cc.alias)          else cc.id end catslug,     (select count(id) table__comments id=a.id) total_comments       jos_content     inner join table_users u on u.id = a.created_by     inner join table_categories cc on cc.id = a.catid