On 13 Dec 2010, at 1:54pm, steve mtangoo 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?
Unfortunately, with the structure you describe this is difficult because it is really three searches: Book 1 from c1v1 onwards ... ... then all the books between book 1 and book 2 (there aren't any) ... ... then all of book 2 up to c2v1. Worse still, the first and last of those have to cope with chapters being broke up into verses. This makes even those parts complicated. I will show one possible solution to make the searching possible with a simple search command. It is to make up a hash string to use as an index. You only need two digits for the Books but let's use three for them all because you need three for chapters and versus (because Psalms has 150 chapters and psalm 119 has 176 verses. So make up a string like this: BbbbCcccVvvv bbb = book number ccc = chapter number vvv = verse number So Book 5 Chapter 6 Verse 7 would be 'B005C006V007'. You can store this string in a new column in your table. Call it 'hash'. And make an index for it. You will have to set the values up somehow: you could write some code in your programming language to do it. To left pad a number with zeros to make it three digits take the number add 1000 to it take the right-most three characters of the result. Now to look up all the verses between a start and end verse you can now just search the hash strings. So for your example search you would do something like SELECT * FROM Bible WHERE hash BETWEEN 'B001C001V001' AND 'B002C003V001' ORDER BY hash and it will find all the right verses in the right order. > My knowledge of SQLite3 > have taken me to a dead end! Try some basic tutorials on SQL. You should find plenty using Google. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
