MySQL query : all records of one table plus count of another table
I have 2 tables: User and Picture. The Picture table has the key of the user. So basically each user can have multiple pictures, and each picture belongs to one user. Now, I am trying to make the following query: I want to select all the user info plus the total number of pictures that he has (even if it's 0). How can I do that? Probably it sounds quite simple, but I am trying and trying and can't seem to find the right query. The only thing I could select is this info, but 开发者_如何学编程only for users that have at least 1 picture, meaning that the Pictures table has at least one record for that key... But I also wanna consider the users that don't have any. Any idea? Thanks!
You may want to try the following:
SELECT u.name,
IFNULL(sub_p.total, 0) num
FROM users u
LEFT JOIN ( SELECT COUNT(*) total, user_id
FROM pictures
GROUP BY user_id
) sub_p ON (sub_p.user_id = u.user_id);
Test case:
CREATE TABLE users (user_id int, name varchar(10));
CREATE TABLE pictures (user_id int);
INSERT INTO users VALUES (1, 'Joe');
INSERT INTO users VALUES (2, 'Peter');
INSERT INTO users VALUES (3, 'Bill');
INSERT INTO pictures VALUES (1);
INSERT INTO pictures VALUES (1);
INSERT INTO pictures VALUES (2);
INSERT INTO pictures VALUES (2);
INSERT INTO pictures VALUES (2);
Result:
+-------+-----+
| name | num |
+-------+-----+
| Joe | 2 |
| Peter | 3 |
| Bill | 0 |
+-------+-----+
3 rows in set (0.00 sec)
SELECT u.name, COUNT(p.picture) AS number
FROM User u
LEFT JOIN Picture p
ON u.id = p.userid
GROUP BY p.userid
精彩评论