MySQL query: all records of one table plus the number of another table

Question

I have 2 tables: User and Picture. The Picture table has a user key. Thus, each user can have several images, and each image belongs to one user. Now I'm trying to make the following query: I want to select all the user information plus the total number of photos that he has (even if it is 0). How can i do this? It probably sounds pretty simple, but I try and try and can’t find the right query. The only thing I could choose was this information, but only for users who have at least 1 image, which means that in the Photos table there is at least one entry for this key ... But I also want to consider users who do not have any ideas? Thank you

Answer 1

You can 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)

Answer 2

SELECT u.name, COUNT(p.picture) AS number
FROM User u
LEFT JOIN Picture p
  ON u.id = p.userid
GROUP BY p.userid