CODEWITHSUNDEEP
mysqlsql
santa
santa

Reputation: 12512

Column join to merge values

I have a table with languages terms. 

VAR   |  EN  |   ES   |  FR
---------------------------
CR   |  car | coche  | voiture

Default is English and it has all terms, however some other languages, like Spanish of French can have terms missing. I need to run a query and get the terms into an array based on the language, but if the term in that language is missing, I need it to use corresponding English term, instead of having blank value.

I was thinking along the following lines:

SELECT * 
FROM terms
WHERE lang = "ES"

But I need all VARs and I don't see how I can pull them that way... Just having a mental block, probably need more coffee...

Upvotes: 0

Views: 41

Answers (2)

Cristian Greco
Cristian Greco

Reputation: 2596

You can use the IFNULL function this way:

SELECT var, IFNULL(es, en) FROM terms

so that if the value in es is null, then en will be returned.

Another option would be the more generic COALESCE function:

SELECT var, COALESCE(fr, es, en) from terms

which just returns the first non null value of the list (in case you want to handle a series of fallback values).

Upvotes: 2

Grzegorz Oledzki
Grzegorz Oledzki

Reputation: 24281

I don't have a copy of MySQL in front of me to check it, but I would try:

SELECT var,
       en, 
       CASE WHEN es IS NULL THEN en ELSE es END as 'es_or_en',
       CASE WHEN fr IS NULL THEN en ELSE fr END as 'fr_or_en'
FROM terms

Upvotes: 1

Related Questions