The query does not return any lines I thought

I have this query

SELECT category_name, categories.category_id, problems.problem_id, COUNT(problems.problem_id) as num_problems
FROM categories
JOIN problem_categories
ON problem_categories.category_id = categories.category_id
JOIN problems
ON problems.problem_id = problem_categories.category_id
WHERE is_top = 1
GROUP BY category_name, categories.category_id, problems.problem_id;

It returns 2 rows. But there are about 9 items that match the is_top = 1 clause. I am not sure why they are not returned as well?

Here is what currently gets returned:

+---------------+------------+--------------+
| category_name | problem_id | num_problems |
+---------------+------------+--------------+
| Entertainment |         46 |            1 |
| Home          |         49 |            2 |
+---------------+------------+--------------+
2 rows in set (0.00 sec)

Here are my tables:

mysql> describe categories;
+----------------------+---------------+------+-----+---------+----------------+
| Field                | Type          | Null | Key | Default | Extra          |
+----------------------+---------------+------+-----+---------+----------------+
| category_id          | int(10)       | NO   | PRI | NULL    | auto_increment |
| creator_id           | int(10)       | NO   |     | NULL    |                |
| category_name        | varchar(100)  | NO   | UNI | NULL    |                |
| category_description | varchar(5000) | YES  |     | NULL    |                |
| category_date        | date          | NO   |     | NULL    |                |
| is_top               | tinyint(1)    | YES  |     | NULL    |                |
| problem_count        | int(8)        | YES  |     | NULL    |                |
+----------------------+---------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

mysql> describe problem_categories;
+---------------------+---------+------+-----+---------+----------------+
| Field               | Type    | Null | Key | Default | Extra          |
+---------------------+---------+------+-----+---------+----------------+
| problem_category_id | int(10) | NO   | PRI | NULL    | auto_increment |
| problem_id          | int(10) | NO   | MUL | NULL    |                |
| creator_id          | int(10) | NO   |     | NULL    |                |
| category_id         | int(10) | NO   |     | NULL    |                |
| category_date       | date    | NO   |     | NULL    |                |
+---------------------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> describe problems;
+---------------------+---------------+------+-----+---------+----------------+
| Field               | Type          | Null | Key | Default | Extra          |
+---------------------+---------------+------+-----+---------+----------------+
| problem_id          | int(10)       | NO   | PRI | NULL    | auto_increment |
| creator_member_id   | int(10)       | NO   |     | NULL    |                |
| problem_title       | varchar(100)  | NO   |     | NULL    |                |
| problem_description | varchar(3000) | YES  |     | NULL    |                |
| problem_date        | date          | NO   |     | NULL    |                |
| upvotes             | int(7)        | YES  |     | NULL    |                |
| downvotes           | int(7)        | YES  |     | NULL    |                |
| date_updated        | date          | YES  |     | NULL    |                |
+---------------------+---------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

Any ideas why the other top categories do not get returned with their counts?

Thanks!!

By the way, the counts for the 2 categories which are returned are correct.

---

The join condition problems.problem_id = problem_categories.category_id looks incorrect.

This should presumably be problems.problem_id = problem_categories.problem_id.

You might also want to consider LEFT JOIN to bring back counts of categories with no problems.