Mysql count return Zero if no record found

mysql zero

David King · Oct 7, 2009 · Viewed 45k times · Source

I have a two tables.

cities - id_city, city_name
properties - id_property, id_city, property_name

I want to display cities.city_name and next to it [properties.count(id_city)]

How do I make a query that still returns zero if no records are found instead of NULL, so that I get results like this:

London [123]
New York [0]
Berlin [11]

where "New York" is [0], not NULL and not 1?

Answer

Use an outer join:

select cities.city_name, count(properties.id_city)
  from cities left join properties on cities.id_city = properties.id_city
  group by 1