Select a value in a language if in another is empty

I have a table with key_id, id, text, lang.

I need to retrieve all the text fields (so without limit) in the table in a language (eg. DE), but if in this language the field is empty, I need to retrieve it in English.

Example:

key_id, id  text       lang
1       1   hi         en
2       1   Guten Tag  de
3       2   nothing    en
4       2   ''         de

I need to have as result, searching with languag开发者_开发技巧e DE:

Guten

nothing -> this is because in deutsch it's empty...

How can I?

Your question doesnt make the table structure completely clear so it is hard to write the Sql for you. However, I think what you want to do is select both the default(en) value of the word, AND the current(de) version. It is then a simple matter to supply the chosen value.

select ifnull(de.text, en.text)
from
  words de
  join words en on
    en.id = de.id and
    en.lang = 'en'

where de.lang = 'de'

You can make use of mysql's non-standard group-by-without-aggregate functionality, which instead of grouping by, it selects the first row for each value in the group by. The inner query first orders the data such that the rows are ordered by language preference for each id.

select * from (
    select id, text, lang
    from lang_table
    order by id, field(lang, 'de', 'en') -- this provides the custom ordering preference. you can add more languages as you like
) x
group by id; -- this captures the first (preferred language) row for each id

first i would have separate table for translations like

[t] id text

[t_translation] id language text

select coalesce(t_translation.text, t.text) as translated_text from t left join t_translation on t.id=t_translation.id where language = 'de' this way you have fallback if there is no record with 'de' language (the query is not complete but you get the idea)