How do I Return 0 From a MySQL db When the Term in the Where Clause is Not in the database?

Question

How do I get my mysql database to return 0 if the neighborhood in the WHERE clause doesn't exist? So in the example below, Old Town is not in the database. I'd like the database to return 0 incidents instead of an empty result.

SELECT incidents, 
       neighborhoods 
 FROM `myTable` 
WHERE neighborhoods ='Old Town'

I also tried

SELECT IFNULL(incidents,0), 
       IFNULL(neighborhoods,0) 
  FROM `myTable` 
 WHERE neighborhoods ='Old Town'

Any suggestions would be really appreciated.

Answer 1

SELECT COALESCE(SUM(incidents), 0), 'Old Town'
FROM `myTable`
WHERE neighborhoods = 'Old Town'

Answer 2

You can add COUNT(*) into the select part, which gives you the amount of rows in the result set. As long you want to read only one row it gives you either 0 or 1.

SELECT COUNT(*), Foo, Bar FROM Bla WHERE false;
+----------+-----+-----+
| COUNT(*) | Foo | Bar |
+----------+-----+-----+
|        0 | NULL | NULL |
+----------+-----+-----+

SELECT COUNT(*), Foo, Bar FROM Bla WHERE Bar = 0;
+----------+------------+-----+
| COUNT(*) | Foo        | Bar |
+----------+------------+-----+
|        1 | 2147483647 |   0 |
+----------+------------+-----+

But if you would get more than one row it may fail as you get only one row from the result set.

SELECT COUNT(*), Foo, Bar FROM Bla WHERE Bar >= 0;
+----------+-----+-----+
| COUNT(*) | Foo | Bar |
+----------+-----+-----+
|        7 |   3 |   6 |
+----------+-----+-----+

(And don't forget to use an alias for the COUNT(*) column)

Answer 3

If you need to return 0 from the DB i suggest using a function.

Otherwise, you could check existence from code by using a COUNT() query before executing the actual query.

Answer 4

My take on your issue is to construct a derived table of the neighborhoods values you hope to find, and LEFT JOIN to the actual table:

   SELECT x.neighborhoods,
          COALESCE(mt.incidents, 0) AS incidents
     FROM (SELECT 'Old Town' AS neighborhoods
             FROM DUAL
           UNION ALL
           SELECT 'New Town'
             FROM DUAL) x
LEFT JOIN MYTABLE mt ON mt.neighborhoods = x.neighborhoods