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

Question

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 BY 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.

D'Arcy Rittich · Accepted Answer · 2010-12-13 17:07:13Z

2

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

answered Dec 13, 2010 at 17:07

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brad Christie · Accepted Answer · 2010-12-13 17:04:07Z

0

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.

answered Dec 13, 2010 at 17:04

Brad Christie

102k16 gold badges160 silver badges200 bronze badges

Comments

hol · Accepted Answer · 2010-12-13 17:45:27Z

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.

Collectives™ on Stack Overflow

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

Table:

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table:

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related