Request to count the number of occurrences-CodePudding

I have a table of companies with the company_id and company_name fields. I also have several tables that have a foreign key on the company. I'm trying to find out how many representatives companies have, so I'm making a request to count. But instead of outputting the number of representatives for each company, I get a truncated result. At the same time, if I count only for one table, then everything works correctly.

Edit: I have 20 records in all the tables I described and each company is used at least once

select hakaton.company.compnay_name, count(*) as people_count
from hakaton.company
join hakaton.sponsors on hakaton.company.company_id =  hakaton.sponsors.company_id
join hakaton.mentors on hakaton.mentors.company_id = hakaton.company.company_id
join hakaton.organizers on hakaton.organizers.company_id = hakaton.company.company_id
join hakaton.judges on hakaton.judges.company_id = hakaton.company.company_id
join customers on customers.company_id = company.company_id
group by hakaton.company.compnay_name 
order by people_count desc

I get this

CodePudding user response：

If a company has 3 sponsors and 5 mentors, a JOIN would return 15 rows. (Instead of 8 actual representatives.)

Instead UNION ALL the different groups, then JOIN and aggregate.

select c.company_name, count(*) as people_count
from hakaton.company c
join (select company_id from hakaton.sponsors
      union all
      select company_id from hakaton.mentors
      union all
      select company_id from hakaton.organizers
      union all
      select company_id from hakaton.judges
      union all
      select company_id from hakaton.customers) dt
  on c.company_id = dt.company_id
group by c.company_name 
order by people_count desc