Group rows with a condition in one query?

Question

MySQL

Hello!
There is a request that returns data:
Articles:
id | name | lang_code | translate_id
1 | test | en | 10
2 | article_en | en | 11
3 | article_us | us | 11
As in one request will get:
Articles:
id | name | lang_code | translate_id
1 | test | en | 10
3 | article_us | us | 11
Those if the article has only 1 translation then return en if it has 2 translations then return us
Tried with having does not work. Apparently it is necessary to do subquery. I will be glad if someone can help.

Answer 1

If there are only two options for codes "en" and "not en", then you can try this:
select ifnull(a_not_en.id, a_en.id), ifnull(a_not_en.name, a_en.name), ifnull(a_not_en.lang_code, a_en. lang_code), ifnull(a_not_en.translate_id, a_en.translate_id)
from Articles a_en
left join Articles a_not_en
on a_not_en.id = a_en.id
where a_en.lang_code = 'en'