SQL COUNT with WHERE clause

Question

I have a simple SQL question that is hard for me to understand. Suppose I have the following tables:

company: company_id company_title users: user_id username company_owners: company_id user_id 

Now there can be several users as company owners. Here are some sample data:

 company: 1, "A Company" 2, "B Company" 3, "C Company" users: 1, "A User" 2, "B User" 3, "C User" company_owners: 1,1 1,2 1,3 2,3 

I am trying to create a query (MySQL) that receives company_title, as well as the number of owners for this company, based on the specific owner of the company. So for example:

Example request (in English): get the number of owners for each company for which the "C User" is the owner:

 company_id=1, company_title="A Company", num_owners=3 company_id=2, company_title="B Company", num_owners=1 company_id=3, company_title="C Company", num_owners=0 

I tried:

 SELECT COUNT(user_id), company.* FROM `company` LEFT JOIN `company_owners` ON company_owners.company_id = company.company_id WHERE company_owners.user_id=1 GROUP BY company_id 

But this always gives me ownership of "1", I suppose, because its only COUNTing line, where user_id = 1.

Does anyone have any ideas? If necessary, I can provide more detailed information.

Many thanks!

Answer 1

The WHERE clause should be "where the owner of this company exists with user_id = 1".

 SELECT COUNT(user_id), company.* FROM `company` LEFT JOIN `company_owners` ON company_owners.company_id = company.company_id WHERE EXISTS ( SELECT * FROM company_owners AS co2 WHERE company_owners.company_id = co2.company_id AND co2.user_id = 3 ) GROUP BY company_id 

See how it works on the web: sqlfiddle

Answer 2

 select company.company_id, company_title, count(*) from company join company_owners on company.company_id = company_owners.company_id where exists (select 1 from company_owners co where co.user_id = 1 and co.company_id = company.company_id) group by company.company_id, company_title