Using case when count() is zero?

I'm trying to get a list of articles sorted by number of comments. I've made similar process to get a list sorted by aticle's votes but I don't know why now I don't get it to work.

SELECT a.*,cc.params as catparams,cc.title as cattitle,u.username,
CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug,
CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELS开发者_高级运维E cc.id END as   catslug,
CASE WHEN (COUNT(rs.id)=0) THEN 0 ELSE COUNT(rs.id) END as total_comments
FROM jos_content AS a
INNER JOIN table_users AS u ON u.id = a.created_by
INNER JOIN table_categories AS cc ON cc.id = a.catid
LEFT JOIN table__comments AS rs ON rs.id = a.id

This code return only results if the article has at least one entry in comments table; what I want is get the list of all articles and if there is no entry in comments table return zero as total_comments. I've tried with INNER JOIN, LEFT JOIN and RIGHT JOIN.

Any help is really much appreciated!

I think you need a sub select

SELECT 
    a.*,
    cc.params as catparams,
    cc.title as cattitle,
    u.username,
    CASE 
        WHEN CHAR_LENGTH(a.alias) 
        THEN CONCAT_WS(":", a.id, a.alias) 
        ELSE a.id END as slug,
    CASE 
    WHEN CHAR_LENGTH(cc.alias) 
        THEN CONCAT_WS(":", cc.id, cc.alias) 
        ELSE cc.id END as catslug,
    (SELECT COUNT(id) FROM table__comments WHERE id=a.id) as total_comments 
FROM 
    jos_content AS a
    INNER JOIN table_users AS u ON u.id = a.created_by
    INNER JOIN table_categories AS cc ON cc.id = a.catid