selecting a range of verses from a database

i have a database, with verses from the bible, with those fields: book (book number), chapter (chapter number), verse (verse number), text (the verse)

example: 1 1 1 In the beginning God created the heaven and the earth.

first 1 is for Genesis, second 1 is开发者_Go百科 for chapter 1, third 1 is for verse 1

user gives me something like 1 1:1 - 1 1:4 which means he wants to show Genesis 1:1-4.

what i want to do is something like

SELECT (book*1000000+chapter*1000+verse AS index) FROM bible WHERE index >= 1001001 AND index <=1001004

or

WHERE book*1000000+chapter*1000+verse >= 1001001 AND book*1000000+chapter*1000+verse <= 1001004

I would suggest the single column "index", but instead of calculating it each time for the query (which performs 1000 times slower), do a one off population from the formula "book*1000000+chapter*1000+verse".

UPDATE bible
SET index = book * 1000000 + chapter * 1000 + verse

Let's say you have controls in the form to choose book, chapter and verse, both for start and end. You would then build the filters as

SELECT index, verse
FROM bible
WHERE index >=
    Forms!f!startBook * 1000000 + Forms!f!startChapter + 1000 + Forms!f!startVerse
AND index <=
    Forms!f!endBook * 1000000 + Forms!f!endChapter + 1000 + Forms!f!Verse

If you must use the calculated column each time (I have more than an inkling that it is more than 1ms), then

SELECT index_, verse
FROM (select book * 1000000 + chapter * 1000 + verse as index_, * from bible) B
WHERE index_ >= 1 AND index_ <= 2

Where the two ?'s are ASP command placeholders for parameters. Precalculate the index using the same formula

startIndex = Request("sbook") * 1000000 + Request("schapter") * 1000 + Request("sverse")

this works: SELECT text FROM bible WHERE book*1000000+chapter*1000+verse >=" & from & "AND book*1000000+chapter*1000+verse <=" & last