steve mtangoo <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
