3

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?

Improve this question
4
  • Hi, welcome to the site. Please post the actual table definitions and sample data as well as expected output
    – Tom V
    Commented Dec 16, 2015 at 20:32
  • 1
    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.
    – jkavalik
    Commented Dec 17, 2015 at 7:03
  • Is there a way to do this with a single join?
    – ejj
    Commented Dec 17, 2015 at 8:03
  • 1
    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).
    – jkavalik
    Commented Dec 17, 2015 at 8:30

1 Answer 1

Reset to default
3

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)

Improve this answer
5
  • 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.
    – Magier
    Commented Dec 17, 2015 at 12:32
  • 1
    @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.
    – jkavalik
    Commented Dec 17, 2015 at 12:36
  • 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
    Commented Dec 17, 2015 at 12:41
  • 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.
    – Magier
    Commented Dec 17, 2015 at 13:02
  • @Magier yes, I mention that after the fiddle link, but went with the info from the question.
    – jkavalik
    Commented Dec 17, 2015 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.