mysql join with condition

conditional-statements, join, mysql, sql

Solution

SELECT Category.id,
        COALESCE(a.lang, b.lang) lang,
        COALESCE(a.title, b.title) title
FROM   categories AS Category
      LEFT JOIN category_i18ns AS a
           ON a.category_id = Category.id AND a.lang = 'de'
      LEFT JOIN category_i18ns AS b
           ON b.category_id = Category.id AND b.lang = 'en'

- SQLFiddle Demo

Problem

I have two table with this structure ``` categories -------------------------------- id | create 1 | 2012-12-01 12:00:00 2 | 2012-12-01 12:00:00 category_i18ns -------------------------------- category_id | lang | title 1 | en | home page 1 | de | HomE PAGEa 1 | fa | خانه 2 | en | about ``` some categories have all languages data and some another havnt. for example category id 2 just have `en` translated title. i want to have a query that first check the current language for retrieve title and then if not exist then show another ones. i have a query to get current category data with i18n data in this current example `de` is default language. for category id `1` we have `de` title but for category `2` we dont. so i want english version as title of category ``` SELECT * FROM `mhf_swe_ndzhmju3_test`.`categories` AS `Category` LEFT JOIN `mhf_swe_ndzhmju3_test`.`category_i18ns` AS `CategoryLocale` ON ( `CategoryLocale`.`category_id` = `Category`.`id` AND `CategoryLocale`.`lang` = 'de') ``` desire out put for 'de' language ``` Category.id CategoryLocale.title CategoryLocale.lang 1 HomE PAGEa de 2 about en ```

Original source