steve mtangoo wrote: > I used your query and devised this which works: > > SELECT * FROM Bible WHERE ID BETWEEN > (SELECT ID FROM Bible WHERE Book=64 AND Chapter=1 AND Verse=1) > AND > (SELECT ID FROM Bible WHERE Book=66 AND Chapter=1 AND Verse=3); >
You might also want to redefine your table so it reflects really what is stored in it. The least unit of distinction in your table is a verse, so, since the plural of verse is verses I would call the table "verses" versus Bible which is worse (hmmm). CREATE TABLE verses ( id INTEGER PRIMARY KEY, verse INTEGER, chapter INTEGER, book INTEGER ); assuming you use only numbers to identify the above entities. Note, redefining this way won't affect your query -- it will only make your programming simpler and easier to understand. Remember, you table really contains "verses," not "Bible." An additional strategy would be to create a composite primary key out of book, chapter, and verse, which is exactly what Igor and Richard suggested, but multiplying Bible by 1000_000 and Chapter by 1000, and so on. That would be kinda like the new sqlite versioning system of 3070400. Just create a style, and stick to it. > On Mon, Dec 13, 2010 at 6:25 PM, Puneet Kishor<[email protected]> > wrote: > >> >> steve mtangoo wrote: >>> No problem. >>> Now only If I could get the ID of the limits (that is ID for Book 1 >> Chapter >>> 1 and Verse 1 for example) and the end limit, my problem will be >>> solved! >> Well, that is simple... >> >> SELECT ID >> FROM Bible >> WHERE Book = 1 AND Chapter = 1 AND Verse = 1 >> >> same for the ending verse. >> >> In fact, as you possibly noted, if the ID is sequentially assigned to >> each verse (assuming, the verses have been inserted in the desired >> order), you could just find out the IDs of the lower and higher verses, >> and then use those to find all the verses in between. >> > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Fellow http://creativecommons.org/about/people/fellows#puneetkishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --------------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science =========================================================================== _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
