MYSQL show 0 even if results do not exist

Question

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 countries 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?

Answer 1

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.