xztCHn5j

1MySQL, select rows if column equals ? else select using default value
2optionID   optionName  optionValue  languageID  
3 -----------------------------------------------
4 1          opt1        Language     1-1  
5 2          opt1        Language     2-2  
6 3          opt2        Language     1-1
7	
8SELECT t3.optionName, 
9       t3.optionValue
10  FROM (SELECT t2.optionName, 
11               t2.optionValue 
12          FROM tbl_options t2
13         WHERE t2.optionName IN ('opt1', 'opt2') 
14           AND (t2.languageID = 2 OR t2.languageID = 1)
15      ORDER BY t2.languageID DESC) t3
16GROUP BY t3.optionName
17	
18select t.optionName, t.optionValue
19from (
20    SELECT optionName, max(languageID) as MaxLanguageID
21    FROM tbl_options   
22    WHERE optionName IN ('opt1', 'opt2') 
23        AND languageID in (1, 2)  
24    group by optionName
25) tm
26inner join tbl_options t on tm.optionName = t.optionName
27    and tm.MaxLanguageID = t.LanguageID
28order by t.optionName
29	
30select * from tbl_options t1  
31where languageid = 1  
32  and not exists 
33           (select * from tbl_options t2  
34                 where languageid <> 1  
35                   and t1.optionName = t2.optionName ) 
36union
37select * from tbl_options  
38where languageid = 2 ;
39	
40select * from tbl_options t1  
41where languageid = 1  
42  and not exists 
43           (select * from tbl_options t2  
44                 where languageid <> 1  
45                   and t1.optionName = t2.optionName ) 
46and optionName IN ('opt1', 'opt2')
47union
48select * from tbl_options  
49where languageid = 2 
50and optionName IN ('opt1', 'opt2');
51	
52create table tbl_options (optionID number, optionName varchar2(50), optionValue varchar2(50), languageId number);
53insert into tbl_options values (1,'opt1','Language 1',1);
54insert into tbl_options values (2,'opt1','Language 2',2);
55insert into tbl_options values (3,'opt2','Language 1',1);
56select * from tbl_options;    
57
58select * from tbl_options t1  
59where languageid = 1  
60  and not exists 
61           (select * from tbl_options t2  
62                 where languageid <> 1  
63                   and t1.optionName = t2.optionName ) 
64union all
65select * from tbl_options  
66where languageid = 2
67	
68Table created.
691 row created.
701 row created.
711 row created.
72
73  OPTIONID OPTIONNAME OPTIONVALUE  LANGUAGEID
74---------- ---------- ------------ ----------
75         1 opt1       Language 1   1
76         2 opt1       Language 2   2
77         3 opt2       Language 1   1
78
793 rows selected.
80
81  OPTIONID OPTIONNAME OPTIONVALUE  LANGUAGEID
82---------- ---------- ------------ ----------
83         3 opt2       Language 1   1
84         2 opt1       Language 2   2
85
862 rows selected.