CODEWITHSUNDEEP
phpmysqlrange
Joshua Wieczorek
Joshua Wieczorek

Reputation: 735

Select Complex Range from MySQL Database

I am trying to select a range of data from a mysql database here is my database structure.

MySQL Database in phpMyAdmin

I am trying to select something like this: Geneses (is book 1 of the bible) and chapters 1 to 3 verse 21 (Genesis 1-3:23). I have all of the data parsed correctly, however, I cannot seem to be able to create a single SQL query that will be suffice.

The following is my current syntax which only selects from chapters 1-3 where verse numbers are <= 21. I need it to only use the verse parameter when it gets to the last chapter, not apply the verse <= 21 to every chapter in between 1 and 3.

SELECT `book`, `chapter`, `verse`, `text` 
from `bible` 
WHERE version = 'kjv' 
AND book = 1 
AND chapter >= 1            
AND ( chapter <= 3 AND verse <= 21 )

I put the sql statements into two statements to maybe give a clearer picture of what I am trying to accomplish. I am just trying to figure out how (if it is possible) to combine theses 2 statements and still have the same response. Now I just query twice then merge the results.

SELECT `book`, `chapter`, `verse`, `text` 
from `bible`
WHERE version = 'kjv'
AND book = 1 
AND chapter >= 1
AND chapter <= 2

SELECT `book`, `chapter`, `verse`, `text`
from `bible`
WHERE version = 'kjv'   
AND book = 1 
AND chapter = 3                 
AND verse <= 10

Upvotes: 0

Views: 91

Answers (2)

Professor Abronsius
Professor Abronsius

Reputation: 33813

I'm not entirely sure if this is what you meant but hopefully:

select distinct
    `book`, `chapter`, `verse`, `text` 
    from `bible` 
    where (
            case
                when `chapter` < ( select max( `chapter` ) from `bible` where `book`=1 ) then
                    `version`='kjv' and `book`=1 and `chapter` in ( 1,2,3 )
                else
                    `version`='kjv' and `book`=1 and `chapter` in ( 1,2,3 ) and `verse` <= 21
            end
        );

Upvotes: -1

juergen d
juergen d

Reputation: 204766

SELECT `book`, `chapter`, `verse`, `text` 
from `bible` 
WHERE version = 'kjv' 
AND book = 1 
AND 
(
  chapter in (1,2)
  OR (chapter = 3 AND verse <= 21)
)

Upvotes: 2

Related Questions