"BareFeetWare", on Saturday, May 21, 2011 9:15 PM wrote...
> Hi Jose, > >> I would like to get a bunch of records of IDs that I already know. For >> example, this table called Jobs, >> rec,...,data,... >> 1,...,aaa,... >> 2,...,zzz,... >> ... >> ... >> 99,...,azz,... > > In addition to the pure syntax answer of other, I suggest you also > consider your broader application of this query. From where are you > getting the IDs in the first place? If you are getting them from a > preliminary query, you can probably combine both queries into one for much > better SQL and performance. Do as much of the logic in SQL as you can to > create internally consistent databases and less application code. It saves > a lot of converting results back and forward from SQL to application > objects. > > For example, if your schema is: > > create table Jobs > ( ID integer primary key not null > , Title text collate nocase > , Company text collate nocase > , Date date > -- and more columns > ) > ; > create table "Job Skills" > ( ID integer primary key not null > , Job integer not null references Jobs(ID) > , Skill text not null collate nocase > , unique (Job, Skill) > ) > ; > > (You should instead normalize the "Job Skills" table by using a third > "Skills" table, but that's another story.) > > And your two queries are: > > select Job from "Job Skills" where Skill = "SQLite"; > > which gives you a list of Job IDs, which you're then re-injecting into a > second query: > > select * from Jobs where ID in (87, 33, 27,2, 1) > order by ID desc > ; > > You can instead combine into one query: > > select * > from Jobs join "Job Skills" on Jobs.ID = "Job Skills".Job > where Skill = "SQLite" > order by Jobs.ID desc > ; > > or, if your prefer: > > select * > from Jobs > where ID in (select Job from "Job Skills" where Skill = "SQLite") > order by Jobs.ID desc > ; I wish I understood everything you said, it's the "it's not you, it's me," excuse. But I think after I google normalize and I read your post a few more times, I can start to appreciate it. However, thanks for this. This is exactly what I need. Because of lack of funding, I've written a very sophisticated PM tool with pricing and other tools using shared DB. The SharedDB file is about 600 megs shared over a network drive and it's getting slow, but if I get the specific record ID only with the select that I want, it's a lot faster than getting the select with all the items in one shot. SQLite probably does something in the back ground to get things faster when addressed specifically to an ID. Thanks for your insightful explanation. josé > -- > Comparison of SQLite GUI tools: > http://www.barefeetware.com/sqlite/compare/?ml > -- > iPhone/iPad/iPod and Mac software development, specialising in databases > develo...@barefeetware.com > > _______________________________________________ > 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
