CODEWITHSUNDEEP
mysqlsqldatabase
Stephen
Stephen

Reputation: 15

How to query tables for multi language records with default language

I have a problem stuck it. I have a category table and category_translate table to support multi languages.

I want the query that can search all categories with my specified language. If language record not exists, it will return default language (i.e. English). I searched a lot but cannot find a solution. Can anyone help to suggest how to resolve it. 

category
---------------
id

category_translate
--------------------
id
category_id
language_id
is_default
name

Except Result:

id   language_id      name.         is_default
------------------------------------------------
1    'en'             'Food'        1
1    'zh'             'Food-ZH'     0
2    'en'             'Cloth'       1



SELECT a.id, b.language_id, b.name, b.is_default
FROM category a, category_translate b
WHERE a.id = b.category_id AND language_id = 'zh'



id   language_id      name          is_default
------------------------------------------------
1    'zh'             'Food-ZH'     0


The above query can get categories with 'zh' language but cannot get other categories with default language 'en'

Upvotes: 1

Views: 1443

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

  1. Never use commas in the FROM clause.
  2. Always use proper, explicit, standard JOIN syntax.
  3. Use meaningful table aliases!

You can solve your problem with LEFT JOIN. Two of them in fact:

SELECT c.id,
       COALESCE(ct.language_id, ctdefault.language_id) as language_id,
       COALESCE(ct.name, ctdefault.name) as name,
       (ct.language_id is null) as is_default
FROM category c LEFT JOIN
     category_translate ct
     ON ct.category_id = c.id AND
        ct.language_id = 'zh' LEFT JOIN
     category_translate ctdefault
     ON ctdefault.category_id = c.id AND
        ctdefault.is_default ;

I'm a little unclear on how is_default is determined. If the language you are searching for is the default, should the value be true or not?

Upvotes: 1

Related Questions