How to retrieve all the data from the Left Table while using Group by on 2nd table with Having Condition

Question

Consider three tables,

1. Team
2. Members (Each member belongs to some team)
3. Tasks (each task is performed by some member)

Tasks Table

t_id      member_id
1       1
2       1 
3       2
4       1

Members Table

id      name      team_id
1       Ali       1
2       Khalil    1
3       Bilal     1
4       John      2
5       Smith     2

Now the result I want is the complete details of the Members Table of A PARTICULAR TEAM along with the Number of Total Tasks each member has performed.

To solve this, I wrote this query,

select m.*, count(t.member_id) as 'Tasks' 
from tbl_member m 
left join tbl_task t on m.m_id = t.member_id 
group by t.member_id 
having m.team_id = :team_id

where team_id can be any variable given by the user.

When I run this query for team_id = 1, I get these results (only printing Member Names and his total tasks)

m_name     Tasks
    Ali     3
    Khalil  1 

As you can see, it skips Bilal who is also part of Team_ID = 1 but because he has performed 0 Tasks, it doesn't print Bilal (even though I used left join)

Similarly, if I use Team_ID = 2, I get these reuslts,

  m_name     Tasks
    John     0
    

It now prints John (who has done 0 Tasks) but it doesn't print Smith who also is part of Team 2 but has not done any task.

So, basically, the query is missing all those people who have done 0 tasks (unless all team members have done 0 tasks. In such a case, it only prints the first member of that team and skips the other, like in the case of Team ID = 2)

Can anyone please tell me how do I fix this? I want to print all the members of one team along with their count, even if their total task count is zero. Please note that it is not compulsory that this must be done using Joins. This can also be done with Subqueries but again, I couldn't make the right logic with subqueries either.

Answer 1

Your GROUP BY and HAVING are undoing the LEFT JOIN. Try this:

select m.*, count(t.member_id) as Tasks
from tbl_member m left join
     tbl_task t
     on m.m_id = t.member_id and
        m.team_id = :team_id
group by m.m_id;

Answer 2

You can use subquery to get the number of task done without any left join or group by clause.

DB-Fiddle:

Schema and insert statements:

 create table tbl_task(t_id int,      member_id int);
 insert into tbl_task values(1,       1);
 insert into tbl_task values(2,       1); 
 insert into tbl_task values(3,       2);
 insert into tbl_task values(4,       1);

 create table tbl_member(id int, name varchar(100),      team_id int);
 insert into tbl_member values(1,       'Ali'       ,1);
 insert into tbl_member values(2,       'Khalil'    ,1);
 insert into tbl_member values(3,       'Bilal'     ,1);
 insert into tbl_member values(4,       'John'      ,2);
 insert into tbl_member values(5,       'Smith'     ,2);

Query:

 select m.*,(select count(t_id)from tbl_task t where t.member_id=m.id)  as 'Tasks' 
 from tbl_member m 
 where m.team_id=1

Ouput:

id	name	team_id	Tasks
1	Ali	1	3
2	Khalil	1	1
3	Bilal	1	0

db<>fiddle here

Answer 3

Group by the left table columns, m.id ..

select m.*, count(t.member_id) as 'Tasks' 
from tbl_member m 
left join tbl_task t on m.id = t.member_id 
where m.team_id = :team_id
group by m.id, m.name, m.team_id

I'm using column names from your table definitions. Your query uses different naming. Correct it as needed.

Answer 4

Gordon's answer is, of course, correct. There's another approach you might take however. It involves this subquery to count the tasks.

        select member_id, count(*) numb
          from tbl_task
         group by member_id

Then you left join that to your members table.

select m.*, t.numb as 'Tasks' 
 from tbl_member m 
 left join (  select member_id, count(*) numb
                 from tbl_task
                group by member_id
           ) t on m.m_id = t.member_id 
 where m.team_id = :team_id

This query pattern uses the main LEFT JOIN aggregate pattern, where the aggregate table contains either zero or one row corresponding to the main table. You may get some NULL values from team members who haven't done any tasks. You can fix that with COALESCE().

select m.*, COALESCE(t.numb, 0) as 'Tasks' 

I wrote this up because I find the main LEFT JOIN aggregate pattern very useful for various report queries. For example, you might need this to get aggregates by member from two different tables. If you don't use the pattern you'll get a combinatorial explosion and high numbers. Here's an example counting absences as well as tasks.

select m.*, t.numb as 'Tasks', a.numb as 'Absences'
 from tbl_member m 
 left join (  select member_id, count(*) numb
                 from tbl_task
                group by member_id
           ) t on m.m_id = t.member_id 
 left join (  select member_id, count(*) numb
                 from tbl_absence
                group by member_id
           ) a on m.m_id = t.member_id 
 where m.team_id = :team_id

Your original query didn't work correctly because you can convert a LEFT JOIN into an ordinary JOIN by mentioning columns from the second table in WHERE or HAVING clauses. That's because both NULL = value and NULL <> value always is false, so any WHERE criterion except WHERE (col IS NULL OR col = val) will not be met.