在1个查询中选择类别，子类别和主题数

Question

I have the following query:

SELECT
        c.frm_category_id,
        c.name,
        c.slug,
        s.frm_category_id,
        s.name,
        s.slug,
        IFNULL(COUNT(t.frm_topic_id),0)
FROM
        frm_categories AS c
LEFT JOIN
        frm_categories AS s
    ON
        c.frm_category_id = s.parent_frm_category_id
LEFT JOIN
        frm_topics AS t
    ON
        s.frm_category_id = t.frm_category_id                       
WHERE
        c.active = 1
    AND
        s.active = 1
ORDER BY
        c.frm_category_id ASC,
        s.frm_category_id ASC

What I want to accomplish is the following:

I want to select ALL parent categories (even if they don't have subcategories), and I want to count all the topics in every subcategory (a topic can only be posted in a subcategory, not in a category).

This query works half: it only selects the categories with subcategories, and only the subcategories which have topics in it.

Can someone fix this problem? Or give me a useful tip to solve this?

Thanks in advance!

Answer 1

Miss group by logic with the frm_topics table, and better put the filter in on clause instead of where clause during outer join.

SELECT
        c.frm_category_id,
        c.name,
        c.slug,
        s.frm_category_id sub_cate_id,
        s.name,
        s.slug,
        IFNULL(tpc_count,0)
FROM
        frm_categories AS c
LEFT join 
        frm_categories AS s
    on 
        c.frm_category_id = s.parent_frm_category_id and c.active = 1 and s.active = 1
LEFT JOIN
(select frm_category_id, count(1) tpc_count from 
        frm_topics group by frm_category_id)AS t
    ON
        s.frm_category_id = t.frm_category_id  

sql fiddle