0

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)

  • Simply change the join between company and user to an inner join... – dnoeth Oct 13 '15 at 19:58
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.