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)