Fallback to known existing records when a record is missing Related to this question, I want to return module contents for various regions/languages. I built my site in English so I know I have all of those pieces, but since I'm getting translations from an outside agency I might be missing pieces. We want those pieces to fallback to English when in doubt.

Here are my tables:

languages

id | tag   | region        | language
--   -----   -------------   --------
1    en-US   United States   English
2    es-ES   Spain           Spanish
...


modules

id | module
--   ------
1    header
2    main
3    footer


contents

id | language_id | module_id | content
--   -----------   ---------   -------
1    1             1           This is my header
2    1             2           This is my main
2    1             3           This is my footer
3    2             1           Este es mi encabezado
4    2             3           Este es mi pie de página


See here, I am missing the 'main' module (module_id=2) for 'es-ES' (language_id=2)

Here's how I'm pulling my data:

SELECT m.module, m.content
FROM `modules` m
LEFT JOIN contents c ON m.id = c.module_id
LEFT JOIN languages l ON c.language_id = l.id
WHERE l.tag = 'es-ES' OR l.tag IS NULL


which returns

module | content
------   -------
header   Este es mi encabezado
main     null
footer   Este es mi pie de página


I want that null value for 'main' to fallback to the English version ("This is my main")
Is there a simple way to do this that I'm missing?
You need something similar to

SELECT m.module, 
       COALESCE(c_esp.content, c_eng.content) content
FROM `modules` m
LEFT JOIN ( contents c_esp, 
            languages l_esp ) ON (     m.id = c_esp.module_id 
                                   AND c_esp.language_id = l_esp.id 
                                   AND l_esp.tag = 'es-ES' )
LEFT JOIN ( contents c_eng, 
            languages l_eng ) ON (     m.id = c_eng.module_id 
                                   AND c_eng.language_id = l_eng.id 
                                   AND l_eng.tag = 'es-ES' )