Check if a field is empty and get value from another column

user_16658142 0
Viewed 569

I have a database with this structure

table_translations

name cat text_en text_es text_pt text_fr
item1 01 one uno un un
item1 02 one_B uno_b un_b
item2 01 two dos dois deux
item3 01 one uno
item4 01 four quatre

table1_items

name cat column1 column2 column3
item1 01 c-1 c-2 c-3
item3 01 c-1 c-2 c-3

table2_items

name cat column1 column2 column3
item1 01 c-1 c-2 c-3
item2 01 c-1 c-2 c-3

With this query I get the translations for each table_items (there are several table_items):

SELECT * 
FROM table1_items, table_translations 
WHERE table_translations.name =  table1_items.name
  AND table_translations.cat = table1_items.cat;
name cat column1 column2 column3 text_en text_es text_pt text_fr
item1 01 c-1 c-2 c-3 one uno un un
item3 01 c-1 c-2 c-3 one uno

However, I would like check if the translation_"language" is empty, then select the value from translation_en. Is that possible?

name cat column1 column2 column3 text_en text_es text_pt text_fr
item1 01 c-1 c-2 c-3 one uno un un
item3 01 c-1 c-2 c-3 one uno one one
mysql sql
2 Answers

You can use conditional IF to check for empty stings

SELECT ti.*,
       tt.text_en,
       IF(tt.text_es =  '', tt.text_en,tt.text_es) as text_es,
       IF(tt.text_pt = '', tt.text_en,tt.text_pt) as text_pt,
       IF(tt.text_fr = '', tt.text_en,tt.text_fr) as text_fr
FROM table1_items ti JOIN
     table_translations tt
     ON tt.name =  ti.name AND tt.cat = ti.cat;
user_51935360

Hmmm . . . you can use coalesce():

SELECT i.*,
       t.text_en,
       coalesce(t.text_es, t.text_en) as text_es,
       coalesce(t.text_pt, t.text_en) as text_pt,
       coalesce(t.text_fr, t.text_en) as text_fr
FROM table1_items i JOIN
     table_translations t
     ON t.name =  i.name AND t.cat = i.cat;

If NULL can mean the empty string, then you can use NULLIF():

SELECT i.*,
       t.text_en,
       coalesce(nullif(t.text_es, ''), t.text_en) as text_es,
       coalesce(nullif(t.text_pt, ''), t.text_en) as text_pt,
       coalesce(nullif(t.text_fr, ''), t.text_en) as text_fr
user_11440350
Related