Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
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?
select cities.city_name, count(properties.id_city)
from cities left join properties on cities.id_city = properties.id_city
group by 1
–
–
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);
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.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.