Select another row if NULL

In a MySQL database I have these tables:

Table 1: News

• news_id int (PK)

• category int (FK)

• rating tinyint

Table 2: News Detail

• news_detail_id int (PK)

• news_id int (FK)

• language_code char

• news_title varchar

• news_detail text

Sample data;

News

news_id | category | rating
--------------------------------
1 | 1 | 3
2 | 3 | 4
3 | 2 | 5
4 | 1 | 1
5 | 2 | 5

News Detail

news_detail_id | news_id | lang_code | news_title | news_details
--------------------------------------------------------------------
1 | 1 | EN | Title1 Eng | Detail 1 Eng
2 | 1 | GER | Ger Titel | Deutsch detail 1
3 | 2 | EN | Title2 Eng | Detail 2 Eng
4 | 3 | EN | Title3 Eng | Detail 3 Eng
5 | 4 | GER | Ger Titel4 | Deutsch detail 4
6 | 5 | GER | Ger Titel5 | Deutsch detail 5

All news have an English translation. In German language some of the translations are missing. Now when I query all news in German language, I want to get English translated row if German translation is missing.

Expected Output

news_id | rating | lang_code | news_title | news_details
-------------------------------------------------------------
1 | 3 | GER | Ger Titel | Deutsch detail 1
2 | 4 | EN | Title2 Eng | Detail 2 Eng
3 | 5 | EN | Title3 Eng | Detail 3 Eng
4 | 1 | GER | Ger Titel4 | Deutsch detail 4
5 | 5 | GER | Ger Titel5 | Deutsch detail 5

Is there a way to accomplish this?

The left-join variant:

select n.news_id, rating,
  coalesce(dg.lang_code, de.lang_code),
  coalesce(dg.news_title, de.news_title),
  coalesce(dg.news_details, de.news_details)
from news n
join news_detail de on (n.news_id = de.news_id and de.lang_code = 'EN')
left join news_detail dg on (n.news_id = dg.news_id and dg.lang_code = 'GER')

http://sqlfiddle.com/#!9/01d421/7 - returns only 3 rows because your sample data does not contain the EN translation for all news as your real data are supposed to.

For completeness, there is a version which picks first available translation (in order 'GER', 'EN') by a dependent subquery - http://sqlfiddle.com/#!9/01d421/9 (in this case it picks the German translation where there is no English one in the sample data, so results differ)

StackExchange Database Administrators Q#123866, answer score: 3