mohammad fatemi mohammad fatemi - 28 days ago 7
MySQL Question

MySQL QUERY to Find the number of posts per user

I have a Database with the following two tables, USERS, POSTS I am looking for a way to get the count of how many posts(postCount) a user has and also if a user doesn't have any posts the postCount will equal to zero

Users Posts
+----+------+ +----+---------+-----------+
| ID | Name | | ID | user_id | Name |
+----+------+ +----+---------+-----------+
| 1 | Bob | | 1 | 1 | Blargg... |
+----+------+ +----+---------+-----------+
| 2 | Jim | | 2 | 1 | Blargg... |
+----+------+ +----+---------+-----------+
| 3 | Jo | | 3 | 2 | Blargg... |
+----+------+ +----+---------+-----------+


In the end I want something like this:

+----+------+
| ID | Count|
+----+------+
| 1 | 2 |
+----+------+
| 2 | 1 |
+----+------+
| 3 | 0 |
+----+------+

Answer

Try this:

SELECT u.ID, u.Name, COUNT(p.Name)
FROM Users AS u
LEFT JOIN Posts AS p ON u.ID = p.user_id
GROUP BY u.ID, u.Name