MySQL, select rows if column equals ? else select using default value

Have an options table that i'm trying to make multi lingual with an English default (languageID 1).

Table:

 optionID   optionName  optionValue  languageID  
 -----------------------------------------------
 1          opt1        Language     1-1  
 2          opt1        Language     2-2  
 3          opt2        Language     1-1

Query:

SELECT t3.optionName, 
       t3.optionValue
  FROM (SELECT t2.optionName, 
               t2.optionValue 
          FROM tbl_options t2
         WHERE t2.optionName IN ('opt1', 'opt2') 
           AND (t2.languageID = 2 OR t2.languageID = 1)
      ORDER BY t2.languageID DESC) t3
GROUP B开发者_StackOverflow社区Y t3.optionName

Is the above query the best way to retrieve rows based on languageID defaulting to 1 if no match is found? Just want to make sure i'm not over complicating.

---

This works on the assumption that English is the lowest language ID:

select t.optionName, t.optionValue
from (
    SELECT optionName, max(languageID) as MaxLanguageID
    FROM tbl_options   
    WHERE optionName IN ('opt1', 'opt2') 
        AND languageID in (1, 2)  
    group by optionName
) tm
inner join tbl_options t on tm.optionName = t.optionName
    and tm.MaxLanguageID = t.LanguageID
order by t.optionName

---

In your select you're going to grab either or not default one of the other. You may want to look in to IFNULL and try for another language, otherwise use English.

---

If you want language=1 if no language=2 exists for a specific optionName then you could also use a union.

select * from tbl_options t1  
where languageid = 1  
  and not exists 
           (select * from tbl_options t2  
                 where languageid <> 1  
                   and t1.optionName = t2.optionName ) 
union
select * from tbl_options  
where languageid = 2 ;

If you only need a specific option you add the optionName

select * from tbl_options t1  
where languageid = 1  
  and not exists 
           (select * from tbl_options t2  
                 where languageid <> 1  
                   and t1.optionName = t2.optionName ) 
and optionName IN ('opt1', 'opt2')
union
select * from tbl_options  
where languageid = 2 
and optionName IN ('opt1', 'opt2');

I tested it and it seems to work:

create table tbl_options (optionID number, optionName varchar2(50), optionValue varchar2(50), languageId number);
insert into tbl_options values (1,'opt1','Language 1',1);
insert into tbl_options values (2,'opt1','Language 2',2);
insert into tbl_options values (3,'opt2','Language 1',1);
select * from tbl_options;    

select * from tbl_options t1  
where languageid = 1  
  and not exists 
           (select * from tbl_options t2  
                 where languageid <> 1  
                   and t1.optionName = t2.optionName ) 
union all
select * from tbl_options  
where languageid = 2 

above outputs to (I did not use mysql but should be the same):

Table created.
1 row created.
1 row created.
1 row created.

  OPTIONID OPTIONNAME OPTIONVALUE  LANGUAGEID
---------- ---------- ------------ ----------
         1 opt1       Language 1   1
         2 opt1       Language 2   2
         3 opt2       Language 1   1

3 rows selected.

  OPTIONID OPTIONNAME OPTIONVALUE  LANGUAGEID
---------- ---------- ------------ ----------
         3 opt2       Language 1   1
         2 opt1       Language 2   2

2 rows selected.