I have 3 tables:

1. Certificate
2. Role
3. GroupCompanies

This is the Certificate table:

 --- -------- ------------ 
| id| name   | company_id |
 --- -------- ------------ 
| 1 | cert01 | 1001       |
| 2 | cert02 | 1001       |
| 3 | cert03 | 1001       |
 --- -------- ------------ 

This is the group_company table:

 ---- --------- ------- ------------ 
| id | cert_id | gc_id | company_id |
 ---- --------- ------- ------------ 
| 1  | 1       | 6     | 1001       |
| 2  | 2       | 6     | 1001       |
| 3  | 3       | 6     | 1001       |
 ---- --------- ------- ------------ 

This is the Role table

 ---- --------- --------- ------------ 
| id | role_id | cert_id | company_id |
 ---- --------- --------- ------------ 
| 1  | 1       | 1       | 1001       |
| 2  | 2       | 1       | 1001       |
| 3  | 1       | 2       | 1001       |
| 4  | 3       | 2       | 1001       |
| 5  | 3       | 3       | 1001       |
 ---- --------- --------- ------------ 

Now cert_id is the foreign key column and its referencing column is id of the certificate table.

Here is the output that I am getting:

 ---- -------- ---------- ------------ 
| id | name   | gc_count | role_count |
 ---- -------- ---------- ------------ 
| 1  | cert01 | 2        | 2          |
| 2  | cert02 | 2        | 2          |
| 3  | cert03 | 1        | 1          |
 ---- -------- ---------- ------------ 

So, the role_count column is perfect after joining but gc_count is showing inaccurate data, it should show count as 1.

Here is the fiddle and what I have tried so far:

fiddle link

select distinct 
    ct.id,
    ct.name,
    count(isnull(gc.cert_id)) as gc_count,
    count(isnull(r.role_id)) as role_count 
from
    certificate ct 
left join 
    group_company gc on ct.id = gc.cert_id 
                     and ct.company_id = gc.company_id 
left join 
    role r on r.cert_id = ct.id 
           and r.company_id = ct.company_id
where 
    ct.company_id = 1001
group by 
    id, name;

CodePudding user response：

You'd aggregate slave tables in the subqueries separately then join them to main table.

SELECT ct.*, gc.gc_count, r.role_count
FROM certificate ct
JOIN (
  SELECT cert_id, company_id, COUNT(*) gc_count 
  FROM group_company 
  GROUP BY cert_id, company_id
  ) gc ON ct.id=gc.cert_id AND ct.company_id=gc.company_id
JOIN (
  SELECT cert_id, company_id, COUNT(*) role_count 
  FROM role
  GROUP BY cert_id, company_id
  ) r ON r.cert_id=ct.id AND r.company_id=ct.company_id

https://dbfiddle.uk/LVWoeKUt

Use LEFT JOIN instead of INNER JOIN if your data needs in it.