Mysql – How to get only those values that has related elements from other table

countjoin;MySQL

I have three tables for a social network: companies, users and countries. Users belong to Companies and Companies have a Country. All tables have their own id column.

  • Table users has a company_id FK column
  • Tables companies has a country_id FK column

The thing is that because of some administrative stuff (Duplicated companies, different names for the same company, etc.) the database has companies with no users (we are actually cleaning all this moving the guys to the companies they really belong to).

I need to get only those companies that actually has users related to it.

Something like

select c.id, c.name, count(co.country_id) as amount from countries as c
left join companies as co on co.country_id = c.id
left join users as u on u.company_id = co.id
where "COMPANY HAS USERS RELATED TO IT"
order by amount desc";

For now I had no problems getting the amounts for each country. But now I need those amounts to only count companies with users related to it. Something that can allow me to avoid having to use a second query inside the "while" with an "if > 0".

By the way, the query starts like that because I'm actually making a list that goes like:

Country (amount of companies)

Best Answer

Do it as GROUP BY aggregation

select
    c.id, c.name,
    count(distinct co.country_id) as amount,
    count(distinct u.id) usercount
from countries as c
left join companies as co on co.country_id = c.id
left join users as u on u.company_id = co.id
group by c.id, c.name
order by amount desc;
Related Question