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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users