[SOLVED] join query with 3 tables

Issue

I have 3 tables in mysql:

t_group:

id | groupid
--------------
1    group1      
2    group2

t_user:

id | userid | groupid
----------------------
1    user1    group1  
2    user2    group1
3    user3    group2
4    user4    group2
5    user5    group2

t_order:

id | orderid | userid
---------------------
1    order1    user1      
2    order2    user2
3    order3    user2
4    order4    user4

I like to get the number of users and the number of orders by group.
My query is as follows:

SELECT t_group.groupid, COUNT(t_user.id) as num_user, COUNT(t_order.userid) as num_order
FROM `t_group` 
left join t_user on t_group.groupid=t_user.groupid
left join t_order on t_order.userid=t_user.userid
GROUP by t_group.groupid

But I got the weird result:

------------------------------
groupid | num_user | num_order
------------------------------
group1    3          3 
group2    3          1  

The actual number of users in group1 is 2, but it shows 3.
Isn’t it possible to get my desired result in a single join query?

Solution

You can try this approach to achieve desired result:

select temp1.groupid,temp1.count_user,temp2.count_order from 
  (select count(distinct (u.userid)) count_user, u.groupid
   from t_group g
   inner join t_user u on u.groupid= g.groupid
   group by u.groupid
  ) temp1
left join
  (select count(distinct (o.orderid))count_order, u.groupid
  from t_group g
  inner join t_user u on u.groupid= g.groupid
  inner join t_order o on u.userid = o.userid 
  group by u.groupid
  ) temp2
on temp1.groupid= temp2.groupid
order by temp1.groupid;

DB Fiddle

Answered By – DB08

Answer Checked By – Marie Seifert (BugsFixing Admin)