sql query - IF EXIST in WHERE clause

Question

I have a query as like

SELECT * 
FROM aTable
LEFT JOIN aTableTranslate
ON aTable.id = aTableTranslate.aTable_id
WHERE
 aTableTransalte.language like 'en'

question is ....

is there any way to filter as like

...

WHERE
 aTableTranslate.language like (IF EXIST_A_FIELD_FOR 'en' THEN 'en' ELSE IF EXIST_A_FIELD_FOR 'jp' THEN 'jp' OR 'cn')

?

I want to show list with 1. visitor's language > 2. english ... > or default language.

is it possible by query?

http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#function_if — Surabhil Sergy, Jan 08 '14 at 10:24

score 2 · Accepted Answer · answered Jan 08 '14 at 10:25

2

You could join to translation tables separately, and pick the first non-NULL translation with COALESCE, like this:

SELECT a.*, COALESCE(t1.translation, t2.translation, t3.translation) as translation
FROM aTable a
LEFT JOIN aTableTranslate t1 ON aTable.id = t1.aTable_id AND t1.language like 'en'
LEFT JOIN aTableTranslate t2 ON aTable.id = t2.aTable_id AND t2.language like 'jp'
LEFT JOIN aTableTranslate t3 ON aTable.id = t3.aTable_id AND t3.language like 'cn'

answered Jan 08 '14 at 10:25

Sergey Kalinichenko

714,442
84
1,110
1,523

Thnks. You exactly answered what I want to know. Thanks. – user2660234 Jan 09 '14 at 08:46
is there any way to use as COALESCE(t1.*, t2.*, t3.*) ? – user2660234 Jan 09 '14 at 08:57
@user2660234 Unfortunately, `COALESCE` cannot be used with asterisks: it produces a single column in the output, so you would need one `COALESCE` for each column of the translation table. – Sergey Kalinichenko Jan 09 '14 at 11:08
thanks again. I want somebody who is making db server to add function to retrieve whole columns on COALESCE. Anyway now I write huge many COALESCEs. :-) – user2660234 Jan 09 '14 at 23:44

score 0 · Answer 2 · answered Jan 08 '14 at 10:35

Try this:

SELECT a.*, COALESCE(IF(t1.language = 'en', t1.translation, NULL), 
                     IF(t1.language = 'jp', t1.translation, NULL), 
                     IF(t1.language = 'cn', t1.translation, NULL)
                   ) AS translation
FROM aTable a
LEFT JOIN aTableTranslate t1 ON aTable.id = t1.aTable_id AND t1.language IN ('en', 'jp', 'cn')

sql query - IF EXIST in WHERE clause

2 Answers2