Select another row if NULL

Question

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?

• Tom V: Hi, welcome to the site. Please post the actual table definitions and sample data as well as expected output
• jkavalik: You can select from the table all the default (EN) rows and LEFT JOIN the translations, then use coalesce to pick the translations when they exist and defaults when not.
• ejj: Is there a way to do this with a single join?
• jkavalik: Afaik no, because you need to combine two rows (EN and GER) into one. Unless you wanted to use a subquery in the SELECT clause, which is not JOIN but is worse (it means a DEPENDENT subquery).

Answer 1

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)

• Magier: This is close but does not return the correct result. Change the first join to LEFT JOIN also because if the details for German do not exist, then there will be no result for English as well.
• jkavalik: @Magier I don't understand what you mean - this returns any news where at least 'EN' details exist, and then left-joins possible 'GER' details to it.
• Magier: Your right. I have oversean the fact that english always exists and thought that there might possibly exist german only. In that case the Join over english to german would fail. But, right your query meets that criteria.
• Magier: If you look at the sample data provided, it is the case that some news only exist in GERMAN, that is why I stumbled about this (news 4+5). They are missing in your result. Changing the first JOIN to LEFT join they show up again. That is what I wanted to point out. Guess ejj has to decide what he / she really needs.
• jkavalik: @Magier yes, I mention that after the fiddle link, but went with the info from the question.