MySQL. How to count the number of topics created in a certain forum category?

Question

MySQL

Category table structure (forum_categories):

+----+-----------+---------------------+--------------+----------+
| id | parent_id | name                | allow_topics | position |
+----+-----------+---------------------+--------------+----------+
|  1 |      NULL | Жизнь сайта         |            0 |        1 |
|  2 |         1 | Конкурсы            |            1 |        1 |
|  3 |         1 | Вопросы/Предложения |            1 |        2 |
+----+-----------+---------------------+--------------+----------+

Forum_topics table structure:

+----+-------------+---------+-------------+--------+--------+---------------------+---------------------+------------+
| id | category_id | user_id | title       | pinned | locked | created_at          | updated_at          | deleted_at |
+----+-------------+---------+-------------+--------+--------+---------------------+---------------------+------------+
|  1 |           2 |       1 | FIRST TITLE |      0 |      0 | 2016-01-18 08:03:53 | 2016-01-18 08:03:53 | NULL       |
+----+-------------+---------+-------------+--------+--------+---------------------+---------------------+------------+

The task is to count the number of topics in a certain category. It should be noted that there can be any nesting of categories. That is, something like this: Forum -> Programming -> Web programming -> Databases -> MySQL and you need to count the number of topics in the "Programming" category.
Or maybe you need to change the structure?

Answer 1

select count(t.id) as cnt, c.* from forum_categories c 
join forum_topics t ON t.category_id = c.id
group by t.category_id