Menu
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
220
First, specify the main table that appears in the FROM clause ( t1 ). Second, specify the table that will be joined with the main table, which appears in the INNER JOIN clause ( t2 , t3 ,…). Third, specify a join condition after the ON keyword of the INNER JOIN clause.
A conditional column join is a fancy way to let us join to a single column and to two (or more) columns in a single query. We can accomplish this by using a case statement in the on clause of our join. A case statement allows us to test multiple conditions (like an if/else if/else) to produce a single value.
You join two tables by creating a relationship in the WHERE clause between at least one column from one table and at least one column from another. The join creates a temporary composite table where each pair of rows (one from each table) that satisfies the join condition is linked to form a single row.
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
