3 table query with count

Question

I'm having huge difficulty with a 3 table query.

The scenario is that TEAM has many or no MEMBERS, a MEMBER could have many or no TASKS. What I want to get is the number of TASKS for every TEAM. TEAM has its own ID, MEMBER holds this as a FK on TEAM_ID, TASK has MEMBER_ID on the TASK.

I want to get a report of TEAM.NAME, COUNT(Person/Team), Count(Tasks/Team)

I have myself so confused, My thinking was to use an Outer Join on TEAM and MEMBER so I have all the teams with any members they have. From here I'm getting totally confused. If anyone can just point me in the right direction so I have something to work from I'd be so greateful

Answer 1

I think you can do what you want with aggregation -- and count(distinct):

select t.name,
       count(distinct m.memberid) as nummembers,
       count(distinct tk.taskid) as numtasks
from team t left join
     member m
     on t.teamid = j.teamid left join
     tasks tk
     on tk.memberid = m.memberid
group by t.name;

Answer 2

Try this out :

SELECT Team.name, COUNT(Person.id_person), COUNT(Tasks.id_task)
FROM Team t,
LEFT JOIN Person p on p.team_id = t.id_team
LEFT JOIN Tasks ts on ts.person_id = p.id_person
GROUP BY p.team_id, ts.person_id

Answer 3

You want to use count distinct:

MySQL COUNT DISTINCT

select t.name as Team,
    count(distinct m.ID) as Member_cnt,
    count(distinct t.ID) as Task_cnt
from team t
   left join member m
       on t.ID= m.TEAM_ID
   left join tasks t
     on t.MEMBER_ID= m.ID
group by t.name;