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

Issue

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.

Solution

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

Answered By - Kazi Mohammad Ali Nur

Answer Checked By - Willingham (PHPFixing Volunteer)