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

Question

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

Tip of today: Always use modern, explicit JOIN syntax. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed! — jarlh
– jarlh, Commented Aug 13, 2021 at 11:51
Probably you need COALESCE(text_es, text_en), COALESCE(text_pt, text_en), COALESCE(text_fr,text_en) — Ankit Bajpai
– Ankit Bajpai, Commented Aug 13, 2021 at 11:56
"empty" in the sense of NULL or empty string? If it's the first COALESCE is your friend. But that would require you explicitly name the columns you want to return. Ie instead of select * use select ..., coalesce(text_es, text_en)... — derpirscher
– derpirscher, Commented Aug 13, 2021 at 11:56

nbk · Accepted Answer · 2021-08-13 12:34:57Z

1

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;

answered Aug 13, 2021 at 12:34

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2021-08-13 13:22:02Z

0

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

edited Aug 13, 2021 at 13:22

answered Aug 13, 2021 at 11:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Marper Over a year ago

Thanks, It works but as I comment before, if the string is empty it doesn't. I had to set to null all the empty fields before.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related