SQL query for MySQL

Question

MySQL

There is a table with the names of cities in different languages.

city_id lang_id name
1 ru Moscow
1 en Moscow
1 de Moskau
2 en Berlin
2 de Berlin

How to get a list of all cities that are in Russian. And those cities that are not translated into Russian, get their English version?

Answer 1

What complex requests and almost certainly thrust into the main pages (that is, they will always be requested), is it really difficult to add the boolean need_translate field, and for a while, without translation, insert English into all languages.
ps it's better to do this: table towns {id, ru,en,fr,...} i.e. by field per language, respectively, requests will be simpler and faster.

Answer 2

select city_id, name
from cities
(where lang_id = 'ru')
or
(city_id not in (select city_id, name from cities where lang_id = 'ru') and lang_id='en')

Answer 3

select IFNULL(t2.name, t1.name) finalName from t t1 left join t t2 on (t1.city_id = t2.city_id and t2.lang_id = 'ru') where t1.lang_id = 'en'

Something like this.
With a join we select Russian translations for cities, then filter by English names.
As a result, we get an English city on the one hand, and a Russian city on the other, or NULL, if not.

Answer 4

SELECT max(name) FROM cities c
where c.lang_id in ("ru","en")
group by c.city_id;

Answer 5

SELECT 
  t.city_id, 
    COALESCE(cru.lang_id, cen.lang_id) AS lang_id,
  COALESCE(cru.`name`, cen.`name`) AS `name`
FROM (
  SELECT city_id FROM city 
    WHERE lang_id IN('ru', 'en') 
    GROUP BY city_id
) AS t 
LEFT JOIN city AS cru ON cru.city_id = t.city_id AND cru.lang_id = 'ru'
LEFT JOIN city AS cen ON cen.city_id = t.city_id AND cen.lang_id = 'en'