anagorn wrote:
I am trying to get "default" value for every ID of JOIN if ON clause were unsucessfull.
In my example, I am trying to get "english" rows, but if they are not available, I would like to get default - "estonian".
I have two tables ie.
table1: id 1 2 3
table2: id - lang - desc 1 - english - EN_A 1 - estonian - ES_A 2 - english - EN_B 2 - estonian - ES_B 3 - estonian - ES_C
now if I join these two tables ie.: SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id AND table2.lang ='english' GROUP BY table1.id;
There is no need for GRUP BY in this query.
I am getting: -- id - id - lang - desc 1 - 1 - english - EN_A 2 - 2 - english - EN_B --
But I would to have "default" row if english is not available:
-- id - id - lang - desc 1 - 1 - english - EN_A 2 - 2 - english - EN_B 3 - 3 - estonian - ES_C --
I have tried with UNION of two JOINS (one for english, one for estonian) but then I am getting duplicate rows with ID=(1,2)
Thanx
I think you need to JOIN to table2 twice, once for each language. Try something like:
SELECT t1.id,
COALESCE(t2a.lang, t2b.lang) AS lang,
COALESCE(t2a.desc, t2b.desc) AS desc
FROM table1 t1
LEFT JOIN table2 t2a ON t1.id=t2a.id AND t2a.lang='english'
LEFT JOIN table2 t2b ON t1.id=t2b.id AND t2b.lang='estonian';COALESCE returns the first non-NULL value in the list, so you'll get estonian when english is missing. See the manual for more <http://dev.mysql.com/doc/mysql/en/comparison-operators.html>.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
