select * from Bible where (book > :1 or (book=:1 and (chapter>:1 or (chapter=:1 and verse>=:1) and (book < :3 or (book=:3 and (chapter<:1 or (chapter=:1 and verse<=:1); Does not work. Is there any issue
On Mon, Dec 13, 2010 at 6:11 PM, Puneet Kishor <punk.k...@gmail.com> wrote: > > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
