Re: [sqlite] Can these 2 queries be combined into one?
On Fri, 18 Jan 2013 23:18:31 -0500, Igor Tandetnik wrote: > On 1/18/2013 11:10 PM, Kai Peters wrote: >> these 2 queries >> >> SELECT lm.ID, lm.Key1, lm.Key2, tr.ISOCode, lm.MaxChars, tr.Description as >> Translation, >> lm.Description FROM LanguageMaster lm INNER JOIN Translations tr ON >> (tr.MasterID = lm.ID) WHERE >> tr.ISOCode = 'DEU'; >> >> >> SELECT lm.ID, lm.Key1, lm.Key2, 'DEU', lm.MaxChars, '' as Translation, >> lm.Description FROM >> LanguageMaster lm WHERE lm.ID not in (SELECT MasterID from Translations); >> >> give me the data I want. Can they be combined into one? >> > SELECT lm.ID, lm.Key1, lm.Key2, coalesce(tr.ISOCode, 'DEU'), lm.MaxChars, > coalesce(tr.Description, '') as Translation, lm.Description FROM > LanguageMaster lm LEFT JOIN > Translations tr ON (tr.MasterID = lm.ID AND tr.ISOCode = 'DEU'); Thanks - so much better than just unioning them! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can these 2 queries be combined into one?
On 1/18/2013 11:10 PM, Kai Peters wrote: these 2 queries SELECT lm.ID, lm.Key1, lm.Key2, tr.ISOCode, lm.MaxChars, tr.Description as Translation, lm.Description FROM LanguageMaster lm INNER JOIN Translations tr ON (tr.MasterID = lm.ID) WHERE tr.ISOCode = 'DEU'; SELECT lm.ID, lm.Key1, lm.Key2, 'DEU', lm.MaxChars, '' as Translation, lm.Description FROM LanguageMaster lm WHERE lm.ID not in (SELECT MasterID from Translations); give me the data I want. Can they be combined into one? SELECT lm.ID, lm.Key1, lm.Key2, coalesce(tr.ISOCode, 'DEU'), lm.MaxChars, coalesce(tr.Description, '') as Translation, lm.Description FROM LanguageMaster lm LEFT JOIN Translations tr ON (tr.MasterID = lm.ID AND tr.ISOCode = 'DEU'); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can these 2 queries be combined into one?
Hi, given CREATE TABLE languagemaster ( ID integer primary key autoincrement, Key1varchar not null, Key2varchar not null, ISOCode varchar not null, Description varchar not null CREATE TABLE translations ( ID integer primary key autoincrement, MasterIDinteger not null, ISOCode varchar not null, Description varchar not null, -- foreign key(MasterID) references languagemaster(ID) ); these 2 queries SELECT lm.ID, lm.Key1, lm.Key2, tr.ISOCode, lm.MaxChars, tr.Description as Translation, lm.Description FROM LanguageMaster lm INNER JOIN Translations tr ON (tr.MasterID = lm.ID) WHERE tr.ISOCode = 'DEU'; SELECT lm.ID, lm.Key1, lm.Key2, 'DEU', lm.MaxChars, '' as Translation, lm.Description FROM LanguageMaster lm WHERE lm.ID not in (SELECT MasterID from Translations); give me the data I want. Can they be combined into one? TIA, Kai ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sum of various rows
"Michael Black" wrote... create table test (id,invoice,transfer,price); insert into test values(10,500,200,0); insert into test values(200,300,300,200); insert into test values(334,3000,200,3000); select sum(invoice),sum(transfer),sum(price) from test where id in (10,200,334); 3800|700|3200 So easy... darn it! :-) Muchas gracias. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sum of various rows
create table test (id,invoice,transfer,price); insert into test values(10,500,200,0); insert into test values(200,300,300,200); insert into test values(334,3000,200,3000); select sum(invoice),sum(transfer),sum(price) from test where id in (10,200,334); 3800|700|3200 Michael Black ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sum of various rows
Greetings. Very newbie question... I have these records... id,invoice, transfer, price 1,0,0,0 ... 10,500.00,200.00,0.00 ... 200,300,300, 200 ... 334,3000, 200,3000 ... what I would like to do is to add invoice, transfer and price. I have this, SELECT sum(invoice),sum(transfer),sum(price) where id = 10,200,334; Of course, it does not work. Any help would be greatly appreciated. Thanks. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users