MYSQL show 0 even if results do not exist

I have a MySql database with 2 tables:

countries
results

• Countries is just and id and a country name.
• Results is a country_id, a value and a date.
• Not all countr开发者_如何学编程ies have results on each date.

How can I create a query that lists all countries with their specific result, but still list them and shows 0 if there is no result on the results table?

Use a LEFT JOIN between the two tables

select c.id, c.name, IFNULL(r.value, 0) value, r.date
from countries c
LEFT JOIN results r on r.country_id = c.id

To show 0 (for the value column) if there is no result, use IFNULL.

what you're looking for is called LEFT JOIN. take a look at this page or ask google for an easy example. for more information, take a look at the mysql documentation:

With following query you will fetch all countries with assigned results to them.

   SELECT *
     FROM countries AS c
LEFT JOIN results AS r
       ON r.country_id = c.id

If there are no values assigned to some countries, there will be null values and according to them you will know you should show 0.

If you want to take into account all the dates that are stored in the results table and to list all the countries for all those dates, replacing results.value with 0 accordingly, then you might probably need to do something like this:

SELECT
  c.id,
  IFNULL(r.value, 0) AS value,
  r.date
FROM countries c
  CROSS JOIN (SELECT DISTINCT date FROM results) d
  LEFT JOIN results r ON c.id = r.country_id AND d.date = r.date