SQLite select query if inner join query doesn’t exists

I have two tables, one for all the foods which contains food id, default name and other foods values.

Another table is for the food name translations which contains food id, language id, translation.

What i want to do is join between these tables by the food id to get the translation for food id = 5 for example and language id = 1 which is Spanish for example. and i do it by:

SELECT * 
FROM Foods 
INNER JOIN FoodNameTranslations USING(Food_ID) 
WHERE Food_ID = 5 
AND Language_ID = 1

Now what if the the ‘FoodNameTranslations’ table doesn’t have a translation for Food_ID 5 and Language 1? then i want to simply get the food row with food id = 5 from the ‘Foods’ table (that contains the default name).

How can i make one query that does this? thanks!

Answer

You would do a LEFT JOIN and put the language ID into the join condition.

SELECT
    COALESCE(t.TranslatedName, f.DefaultName) FoodName 
FROM 
    Foods f
    LEFT JOIN FoodNameTranslations t ON t.Food_ID = f.Food_ID AND t.Language_ID = 1
WHERE
    f.Food_ID = 5