MySQL: Multiple Inner Joins Producing Skewed Count-CodePudding

I have 3 tables, tb_orders, tb_tasks & tb_userconfig which I am trying to query but it seems like when running the query with both inner join statements, the COUNT() is completely skewed however when trying to run the query with only one inner join statement everything seems to work (minus the one column I would be missing)

Example table structure:

tb_orders

| OrderedBy | OrderDate    |
|-----------|--------------|
| persona   | 29/6/21 0:00 |
| persona   | 29/6/21 0:00 |
| personc   | 29/6/21 0:00 |
| personb   | 29/6/21 0:00 |

tb_userconfig

| EmployeeName | Username |
|--------------|----------|
| Person A     | persona  |
| Person B     | personb  |
| Person C     | personc  |

tb_tasks

| AssigneeUser |
|--------------|
| Persom A     |
| Person B     |
| Person C     |
| Person B     |

Query:

SELECT T1.EmployeeName, T1.Username, Count(T2.OrderedBy) AS OrderCount, Count(T3.AssigneeUser) AS TaskCount
FROM tb_userconfig AS T1
INNER JOIN (
    SELECT OrderedBy, OrderDate FROM tb_orders
    ) AS T2 on T1.Username = T2.OrderedBy
INNER JOIN (
    SELECT AssigneeUser FROM tb_tasks
    ) AS T3 on T1.EmployeeName = T3.AssigneeUser
GROUP BY T1.EmployeeName ORDER BY T1.EmployeeName

Desired result:

| EmployeeName | Username | OrderCount | TaskCount |
|--------------|----------|------------|-----------|
| Person A     | persona  | 2          | 1         |
| Person B     | personb  | 1          | 2         |
| Person C     | personc  | 1          | 1         |

Any ideas on why the statement would be resulting in skewed counts and not producing the desired result?

TIA

CodePudding user response：

You can simplify by pre-aggregating the counts.

Without knowing your data I would suggest using outer joins and coalesce to handle no rows as otherwise users with tasks but no orders or vice-versa would be excluded.

select uc.EmployeeName, uc.username,
  Coalesce(o.OrderCount, 0) OrderCount, 
  Coalesce(t.TaskCount, 0) TaskCount
from tb_userconfig uc
left join (
  select orderedby, Count(*) OrderCount
  from tb_orders
  group by orderedby
) o on o.orderedby=uc.username
left join (
  select AssigneeUser, Count(*) TaskCount
  from tb_tasks
  group by AssigneeUser
) t on t.AssigneeUser=uc.username

Demo Fiddle