You're structure is bad for future growth (i.e. multiple languages) as the
query gets really nasty really quickly.
You should normalize this data and your query will never change.
pragma foreign_keys=ON;
CREATE TABLE buttons (
ID integer primary key autoincrement,
Key1 varchar not null,
Key2 varchar not null
);
insert into buttons(key1,key2) values('FORM1','SAVE_BUTTON');
insert into buttons(key1,key2) values('FORM1','HELP_BUTTON');
CREATE TABLE masterlanguages(
ID integer primary key autoincrement,
ISOCode varchar not null
);
insert into masterlanguages (ISOCode) values ('ENG');
insert into masterlanguages(ISOCode) values ('DEU');
CREATE TABLE buttontext (
ID integer primary key autoincrement,
Description varchar not null,
masterlanguage integer,
button integer,
foreign key(masterlanguage) references masterlanguages(id),
foreign key(button) references buttons(id)
);
insert into buttontext (Description,masterlanguage,button)
values('Save',1,1);
insert into buttontext (Description,masterlanguage,button)
values('Help',1,2);
insert into buttontext (Description,masterlanguage,button)
values('Speichern',2,1);
insert into buttontext (Description,masterlanguage,button)
values('Hilfe',2,2);
All you need to do0 is specify what language you want in the query.
sqlite> select * from buttons b1 join buttontext b2 on b1.id=b2.button and
b2.masterlanguage=(select id from masterlanguages where ISOCode='ENG');
ID|Key1|Key2|ID|Description|masterlanguage|button
1|FORM1|SAVE_BUTTON|1|Save|1|1
2|FORM1|HELP_BUTTON|2|Help|1|2
sqlite> select * from buttons b1 join buttontext b2 on b1.id=b2.button and
b2.masterlanguage=(select id from masterlanguages where ISOCode='DEU');
ID|Key1|Key2|ID|Description|masterlanguage|button
1|FORM1|SAVE_BUTTON|3|Speichern|2|1
2|FORM1|HELP_BUTTON|4|Hilfe|2|2
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of Kai Peters
Sent: Monday, January 14, 2013 11:50 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Need help with query
Hi all,
given
CREATE TABLE masterlanguages (
ID integer primary key autoincrement,
Key1 varchar not null,
Key2 varchar not null,
ISOCode varchar not null,
Description varchar not null,
MaxChars integer default 0
);
insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'ENG',
'Save', 0);
insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'ENG',
'Help', 0);
insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'DEU',
'Speichern', 0);
insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'DEU',
'Hilfe', 0);
In addition to the data from
SELECT * FROM MASTERLANGUAGES WHERE ISOCode = 'DEU'
I also need the Description field for the corresponding record (based on
Key1 + Key2) in English so
that I can display the original English description as well as its German
translation.
How can I achieve this?
TIA,
Kai
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
