SQL query for fetching rows in certain language (with default lang)

I have a table with cities:

• city_id
• name
• la开发者_高级运维nguagecode

Sample data (city_id, name, languagecode):

• 1, LondonName, en
• 1, LondonName, de
• 1, LondonName123, jp
• 2, Barcelona, en
• 2, Barcelona123, de,
• 3, Madrid, en

How can I fetch all cities in certain language (i.e. de), but if there there aren't city translation in that language I want fetch translation in default language (i.e. english) for this row.

So, result for languagecode "de" should be like this:

• 1, LondonName, de
• 2, Barcelona123, de
• 3, Madrid, en

Database: MySQL

---

I usually prefer to use JOIN rather than inner select, so here's a second option:

SELECT c.city_id,
IF(c2.name IS NULL,c.name,c2.name) 'name',
IF(c2.languagecode IS NULL, c.languagecode, c2.languagecode) 'languagecode'
FROM cities c
LEFT JOIN cities c2 ON c.city_id = c2.city_id AND c2.languagecode = 'de'
WHERE c.languagecode = 'en'
GROUP BY c.city_id