steve mtangoo wrote: > Hi Igor, > would you explain what are bookLow and chapterHigh? > Do you mean lowest book and highest chapter?
:bookLow and :chapterHigh are place holders for numbers you will provide. Those numbers stand for the lowest numbered book and the highest numbered chapter between which you want to search. > > I want to test this query but please help me to understand it. > select * from Bible where > (book> :bookLow or (book=:bookLow and (chapter>:chapterLow or > (chapter=:chapterLow and verse>=:verseLow) > and > (book< :bookHigh or (book=:bookHigh and (chapter<:chapterHigh or > (chapter=:chapterHigh and verse<=:verseHigh); > > And thanks to everybody who is helping here! > > On Mon, Dec 13, 2010 at 5:13 PM, Igor Tandetnik<itandet...@mvps.org> wrote: > >> steve mtangoo<mwinjili...@gmail.com> wrote: >>> I have a script that is supposed to query the Bible scriptures between >> two >>> intervals. My table is named Bible and have columns: ID (int), Book >> (int), >>> Chapter(int), Verse (int) and Scripture(text). >>> >>> Now the books are unique i.e. 1-66 but chapters keep repeating, and so do >>> verses. Now suppose I want to get scripture between book 1 chapter 1 >> verse 1 >>> and book 2 chapter 3 Verse 1, how do I go about? My knowledge of SQLite3 >>> have taken me to a dead end! >> Here's a straighforward answer: >> >> select * from Bible where >> (book> :bookLow or (book=:bookLow and (chapter>:chapterLow or >> (chapter=:chapterLow and verse>=:verseLow) >> and >> (book< :bookHigh or (book=:bookHigh and (chapter<:chapterHigh or >> (chapter=:chapterHigh and verse<=:verseHigh); >> >> Here's a trick to make it shorter: >> >> select * from Bible where >> book*1000000 + chapter*1000 + verse between >> :bookLow*1000000 + :chapterLow*1000 + :verseLow >> and >> :bookHigh*1000000 + :chapterHigh*1000 + :verseHigh; >> >> Basically, the idea is to assign each verse a unique number (the query >> above assumes there are no more than 1000 verses per chapter and chapters >> per book; adjust multipliers if there are more). You may want to consider >> working with such sequential numbers internally in your program, converting >> between them and (book, chapter, verse) format on input/output only. So, >> define your table as Bible(VerseNumber integer primary key, Scripture text), >> and the query becomes simply >> >> select * from Bible where VerseNumber between :VerseLow and :VerseHigh; >> >> -- >> Igor Tandetnik >> >> _______________________________________________ >> 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 -- 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
