Selecting a range of rows using 3 different indices

Question

My database looks like this:

Book,Chapter,Verse,Scripture
"1","1","1","1text1"
"1","1","2","1text2"
"1","1","3","1text3"
"1","1","4","1text4"
"1","2","1","2text1"
"1","2","2","2text2"
"1","2","3","2text3"

I want to select all the rows from 1,1,1 to 1,2,3.

However my current query will not return row 1,1,4 because 4 is greater than 3.

SELECT * FROM my_table WHERE 
Book >= 1 AND Book <= 1 AND 
Chapter >= 1 AND Chapter <= 2 AND
Verse >= 1 AND Verse <= 3

Answer 1

you need to use an OR statement.

SELECT * FROM my_table WHERE 
(Book = 1 AND 
Chapter BETWEEN 1 AND 2
Verse BETWEEN 1 AND 2)
OR
(/* THE OTHER STATEMENT */)
OR
(/* THE OTHER STATEMENT */)

Edit

And is much more clear if you use the BETWEEN operator

Answer 2

MySQL also supports row constructors. If you wanted (e.g.) 1 1:3 through 1 2:2, use:

SELECT * FROM bible
    WHERE (1,1,3) <= (book, chapter, verse) AND (book, chapter, verse) <= (1,2,2)

For 58 1:3 to 62 4:2,

SELECT * FROM bible
    WHERE (58,1,3) <= (book, chapter, verse) AND (book, chapter, verse) <= (62,4,2)

58 1:4 will be included, as will 59 1:1 and 60 10:10, but not 62 5:1.

I can't find much documentation, but MySQL follows the behavior set down for row comparisons since SQL-92 (note: the link is to a draft version), specifically Section 8.2 "General Rules" 7):

Let R^x and R^y be the two row value constructors of the comparison predicate and let R^x_i and R^y_i be the i-th row value constructor elements of R^x and R^y, respectively. "R^x [comp op] R^y" is true, false, or unknown as follows:
[...]
c) "R^x < R^y" is true if and only if R^x_i = R^y_i for all i < n and R^x_n < R^y_n for some n.

d) "R^x > R^y" is true if and only if R^x_i = R^y_i for all i < n and R^x_n > R^y_n for some n.

e) "R^x <= R^y" is true if and only if R^x = R^y or R^x < R^y.

f) "R^x >= R^y" is true if and only if R^x = R^y or R^x > R^y.

Row comparisons are covered in section 9.2 Joe Celko's SQL For Smarties (link is to 3rd Ed., but the same topic existed in earlier editions).

Answer 3

This is what I've come up with for selecting a range within a book. If I want from 1,1,3 to 1,7,2

SELECT * FROM Bible WHERE Book = 1 AND Chapter = 1 AND Verse >= 3
UNION
SELECT * FROM Bible WHERE Book = 1 AND Chapter >1 AND Chapter <7
UNION
SELECT * FROM Bible WHERE Book = 1 AND Chapter = 7 AND Verse <=2

To select a range across books: 58,1,3 to 62,4,2

SELECT * FROM Bible WHERE Book = 58 AND Chapter = 1 AND Verse >= 3
UNION
SELECT * FROM Bible WHERE Book = 58 AND Chapter > 1
UNION
SELECT * FROM Bible WHERE Book > 58 AND Book < 62
UNION
SELECT * FROM Bible WHERE Book = 62 AND Chapter < 4
UNION
SELECT * FROM Bible WHERE Book = 62 AND Chapter = 4 AND Verse <=2

Answer 4

The only solution I can think of involves adding an additional column for indexing:

ALTER TABLE my_table ADD VerseIndex CHAR(10) NOT NULL, INDEX(VerseIndex(4));

UPDATE my_table SET VerseIndex = CONCAT(
                                        LPAD(Book, 2, '0'),
                                        '-', LPAD(Chapter, 3, '0'),
                                        '-', LPAD(Verse, 3, '0')
                                        );

You should now have an extra column in your table with a text value of '--', which mysql can search alphabetically to give you an exact range:

# find book 2 chapter 4 v 3 through to book 3 chapter 1 v 1 (inclusive)
SELECT * FROM my_table WHERE VerseIndex BETWEEN '02-004-003' AND '03-001-001' ORDER BY VerseIndex;

# find book 5 chapter 4 (whole chapter) through to book 7 end of chapter 9
SELECT * FROM my_table WHERE VerseIndex BETWEEN '05-004-000' AND '07-009-999' ORDER BY VerseIndex;

Answer 5

What's missing from your data model is the fact that Book, Chapter, and verse are hierarchically related.

If for each entry, you also stored single data item that combined book chapter and verse together, searching for a given range would involve just a simple BETWEEN operator.

Let's say that you knew that no book contains more than 99 chapters, and no chapter contains more than 999 verses. You could compute (Book*100+Chapter)*1000+Verse for each item stored, and store that in a separate column. (Yes, I know this is redundant).

Then you could search for that column between (1*100 + 1)1000 + 1 and (1100+2)*1000+5 to find everything from 1,1,1 to 1,2,5.