CODEWITHSUNDEEP
mysql
daliaessam
daliaessam

Reputation: 1666

Simple Mysql select another row if field is empty

I have a multi-lingual table for the country like this:

--------------------------------------------------------------------
 country | lang | name
--------------------------------------------------------------------
 US      | en-US | United States
--------------------------------------------------------------------
 US      | fr    | États-Unis
--------------------------------------------------------------------
 US      | ar | الولايات المتحدة
--------------------------------------------------------------------
 US      | de |
--------------------------------------------------------------------

I want to select the field "name" for the country 'US' for the lang 'de' but if the name field is empty fallback to the language 'en-US'.

so will a query like this should be correct:

select name from countries where country='US' and name<>"" and lang in ('de', 'en-US')

I really need optimized query.

Upvotes: 0

Views: 653

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Here is a way to get the answer:

select name
from countries
where country = 'US' and name<>"" and lang in ('de', 'en-US')
order by lang = 'de' desc
limit 1;

If you have an index on country, lang, name then it should perform well.

EDIT:

This might perform better with the same index:

select coalesce((select name
                 from countries
                 where country = 'US' and name <> "" and lang = 'de'
                ),
                (select name
                 from countries
                 where country = 'US' and name <> "" and lang = 'en-US'
                )
               ) as name

EDIT II:

A standard SQL approach similar to the first is:

select (case when sum(case when lang = 'de' then 1 else 0 end) > 0
             then max(case when lang = 'de' then name end)
             else max(name)
        end) 
from countries
where country = 'US' and name <> "" and lang in ('de', 'en-US');

An aggregation on two rows should be quite fast in any database. However, the coalesce() method should essentially just be two index lookups.

Upvotes: 1

Related Questions