MySQL -- Retrieve rows with specific field condition but with a fallback field condition

Suppose we have these 3 tables,

Language

language_id     name
1               English
2               Tagalog

Color

color_id
1
2
3
4

Color translations

color_translations_id   color_id    language_id   name
1                       1           1             Black
2                       2           1             White
3                       3           1             Red
4                       4           1             Blue
5                       1           2             Itim
6                       2           2             Puti

Suppose I want to get all color translations rows for each color which are in Tagalog but with a fallback language in English. The result should be

color_translations_id   color_id    language_id   name
5                       1           2             Itim
6                       2           2             Puti
3                       3           1             Red
4                       4           1             Blue

Furthermore, if the tra开发者_如何学Gonslation table is like this

color_translations_id   color_id    language_id   name
1                       1           1             Black
2                       2           1             White
3                       3           1             Red
4                       4           1             Blue

Even if there are no Tagalog rows, I should return all 4 of them since my fallback language is in English

Is there a way to do this in SQL?

This was written for SQL Server and I had to change the names a bit, but the idea is there and I didn't use any proprietary features, so it should work. You may need to change the INNER JOINs to just JOINs.

WITH PrimaryLanguageColors AS 
  (SELECT color_id, color_translations_id
   FROM ColorTranslations INNER JOIN Languages
   ON ColorTranslations.language_id = Languages.language_id 
   WHERE Languages.Name = 'Tagalog')
SELECT color_translations_id, color_id, ColorTranslations.language_id,
  ColorTranslations.name 
FROM ColorTranslations INNER JOIN Languages 
ON ColorTranslations.language_id = Languages.language_id
WHERE color_translations_id IN (SELECT color_translations_id 
                                FROM PrimaryLanguageColors)
OR (color_id NOT IN (SELECT color_id FROM PrimaryLanguageColors)
    AND Languages.Name = 'English')