Hi Igor, would you explain what are bookLow and chapterHigh? Do you mean lowest book and highest chapter?
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
