Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – select count of related records between 2 tables using left join

I am trying to use a count(..) to get the count value of matching records from a related table. I’m using MySQL 8.

The idea is simply to count the number of tasks assigned to each user.

If the user has NO tasks, they should still be listed, but with "0" as the task count (thus, I was thinking that a left join is what I needed). I’m hoping to use "u.*" because I will be adding additional fields to the output soon.

This is the SQL that I’m currently working with:

SELECT u.*, t.task_count
FROM users u
LEFT JOIN (
  SELECT count(*) as task_count 
  FROM user_tasks
  WHERE user_tasks.user_id = users.id
  group by user_id
) AS t ON t.user_id = u.id

Here is the DB schema & some sample values:

create table user_tasks (
    id integer primary key,
    user_id integer not null
);

create table users (
    id integer primary key,
    name text not null
);

insert into user_tasks values (1, 1);
insert into user_tasks values (2, 2);
insert into user_tasks values (3, 1);
insert into user_tasks values (4, 1);
insert into user_tasks values (5, 1);
insert into user_tasks values (6, 2);

insert into users values (1, 'joe');
insert into users values (2, 'sally');
insert into users values (3, 'bob');

The output should be this, the count of how many tasks each user has:

1, 'joe', 4
2, 'sally', 2
3, 'bob', 0

At the moment I’m getting "Unknown column ‘users.id’" but I’m unclear why.

Tags:

2

Answers


  1. 0 
    SELECT id, users.name, COALESCE(subquery.task_count, 0) task_count
FROM users
LEFT JOIN (
  SELECT user_id id, COUNT(*) as task_count 
  FROM user_tasks
  GROUP BY 1
) subquery USING (id)
    Login or Signup to reply.
  2. 0

    From your query, you can modify it like this to get the count from a correlated subquery:

    SELECT u.*, (
  SELECT count(*)
  FROM user_tasks
  WHERE user_tasks.user_id = u.id
  group by user_id
) as task_count 
FROM users u;

    For a LEFT JOIN, you can do something like this:

    SELECT u.id, u.name, SUM(IF(ut.id IS NULL,0,1))
  FROM users u
    LEFT JOIN user_tasks ut
  ON ut.user_id = u.id
  GROUP BY u.id, u.name;

    However, instead of using COUNT(), use SUM(IF()) because COUNT() will return bob=1 as there’s 1 row of bob. To clarify, if you run the query returning all columns and without GROUP BY like this:

    SELECT *
  FROM users u
    LEFT JOIN user_tasks ut
  ON ut.user_id = u.id

    you’ll get 1 row of bob in the results and that counted as 1:

    |id | name  |  id  |user_id|
+---+-------+------+-------+
| 1 | joe   |  5   |   1   |
| 1 | joe   |  4   |   1   |
| 1 | joe   |  3   |   1   |
| 1 | joe   |  1   |   1   |
| 2 | sally |  6   |   2   |
| 2 | sally |  2   |   2   |
| 3 | bob   | null |  null |  <--- this is counted as 1 row in COUNT()
+---+-------+------+-------+

    Demo fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search