mysql - Pretty basic SQL query involving count -

let's have following schema

company: -> company_id -> company_name  building_to_company: -> building_id -> company_id 

so each building has own id company id relates single company.

the following query gives 2 columns -- 1 company name, , associated buildings.

select company.company_name, building_to_company.building_id  company, building_to_company  company.company_id = building_to_company.company_id; 

the returned table this:

company name | building id smith banking  2001 smith banking  0034 smith banking  0101 smith banking  4055 reynolds       8191 tradeco        7119 tradeco        8510 

so that's simple enough.

but need bit different. need 2 columns. 1 company name , on right number of buildings owns. , little challenge want list companies 3 or less buildings.

at point real progress i've made coming query above. know how have use count on building_id column , count number of buildings associated each company. , @ point can limit things using where x < 4

you've got in words already. assuming company_name unique, have add explanation work group by clause:

select company.company_name, count(building_to_company.building_id) company inner join building_to_company      on company.company_id = building_to_company.company_id group company.company_name 

(sql fiddle demo of query in action)

---

to limit companies 3 or less buildings, key have use having clause , not where. because want filter based on results of aggregate (count); put, where filters come before aggregation , having come after:

select company.company_name, count(building_to_company.building_id) company inner join building_to_company      on company.company_id = building_to_company.company_id group company.company_name having count(building_to_company.building_id) < 4 

(sql fiddle demo of query in action)