MySQL SELECT + LEFT JOIN + IF query

Question

I have one of simple DB tables named category:

category.id category.name category.lang
1           'apple'       'en'
1           'manzana'     'es'
1           ''            'de'
1           ''            'it'

Variable:

$default_lang = 'en';
$current_lang = 'it';

I would like to make query that returns category_name field according to $current_lang, but if category.name is empty then should use category.name from $default_lang like this:

SELECT * FROM cat_list
    LEFT JOIN category ON cal_list.id = category.id
          AND IF(category.name = $current_lang = '', $default_lang, 'No Lang')

Please let me know, how could i fix this query to make it work?

===

Ok now I have a good Idea:

SELECT * FROM cat_list LEFT JOIN category 
           ON cal_list.id = category.id 
          AND category_lang IN ($curreng_lang, $default_lang)
          AND category_name <> ''

it works fine but should I worry about the "IN" order in case if both languages has name values?

Solution 1

SELECT catname=
CASE name
         WHEN '' THEN (select name from Category where lang=$default_lang limit 1)
         ELSE name
      END
FROM Category where id=$current_lang

Explaination: here Query is using case statement inside select statment, where outer query is using condition "id=$current_lang"

so in select statement it will have name='', so for

catname=
    CASE name
             WHEN '' THEN (select name from Category where lang=$default_lang limit 1)
             ELSE name
          END

name is '', so first condition with when should execute here it will execute query

(select name from Category where lang=$default_lang limit 1) name apple is returned (because language is 'en') and that will be sotred to catname variable. which will be returned in result

If it will get value with something for name first time (id=$current_lang) it will return with it.

Solution 2

Is the category.name NULL or empty string?

If NULL, use COALESCE (Returns the first non-NULL value in the list)

SELECT category.id, 
       COALESCE(category.name, $default_lang) name,
       COALESCE(category.lang, $current_lang) lang
FROM cat_list
     LEFT JOIN category 
           ON cal_list.id = category.id

If Empty String, Use IF

SELECT category.id, 
       IF(category.name = '', $default_lang,category.name) name,
       IF(category.lang = '', $current_lang,category.lang) lang
FROM cat_list
     LEFT JOIN category 
           ON cal_list.id = category.id

Comments

[NullPoiиteя]

please elaborate more so user can understand more easily only code as answer is not a good way to answer

[Ken Tang]

I do use Empty String, but: IF(category.name = '', $default_lang,category.name) name, this will just return 'en' ($default_lang) for 'name' field in case of category.name <> '' ?

[Ken Tang]

Patriks, it is possible to implement it without SELECT in SELECT? because table category has about 800,000 rows :(

[John Woo]

@user1664869 category.name will be display if it is not null or empty string.

[Ken Tang]

Johnny, what do you think if to make it like this: SELECT * FROM cat_list LEFT JOIN category ON cal_list.id = category.id AND category_lang IN ($curreng_lang, $default_lang) ?

[John Woo]

@user1664869 that will only show records that has language og en and it and nothing more.

[Ken Tang]

yes, only two 2 row is ok one is current_lang another one is default_lang, but at the end I need to choose only one that category.name <> ''