Mysql count return Zero if no record found

Question

I have a two tables for ex:

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 return ZERO if no records founds istead on NULL

so I get results like

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

and NEW YORK is [0] not NULL, NOT 1

Answer 1

Use an outer join:

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

Answer 2

The query:

SELECT cities.*, COUNT(properties.id_city) as num FROM cities LEFT JOIN properties on cities.id_city=properties.id_city GROUP BY cities.id_city

should return a 0 count where you want it, although I'm not 100% certain it works that way in MySQL.

Answer 3

I think the following will do it for you, though I haven't tested it. The trick is to get the property counts in one table, and then to left join that table to the cities table, converting NULLs to 0s using the IFNULL function.

SELECT city_name, IFNULL(property_count, 0)
FROM cities
LEFT JOIN
   (SELECT id_city, count(*) as property_count
    FROM properties
    GROUP BY id_city) city_properties
   USING (id_city);