Unable to retrieve rows in sql statement

Question

i want to select books which have chapter 29 and books which have chapter 30 and so on. I tried the code below but it only returns books that have chapter 29 but not 30, 31, 32

SELECT distinct book FROM `bible_kjv` WHERE chapter in (29,30,31,32,33,34,35);

Please how can I rewrite code

Answer 1

Keep in mind that this:

SELECT distinct book FROM `bible_kjv` WHERE chapter in (29,30,31,32,33,34,35);

Is different from this:

SELECT distinct book FROM `bible_kjv` WHERE chapter in ('29,30,31,32,33,34,35');

The first compares chapter to seven integer values. The second compares chapter to a single string, and since chapter is probably an integer, it casts the string to a numeric value. In MySQL, the numeric value of '29,30,31,32,33,34,35' is 29 (it reads the leading digits, and ignores everything past the comma).

I suspect there's more about your code that you didn't share in the question.

I would guess that you tried to use a parameterized query and passed a single string with your list of chapters. This isn't how parameters work. One parameter can take the place of a single scalar value only, not a list of values.

If you want to use parameters, you'll have to use seven parameter placeholders.

Answer 2

you will get record between 29 to 35 then use this query

SELECT b.book FROM Bible b WHERE chapter between 29 to 35;

and if you want record outside of 29 to 35 then use

SELECT b.book FROM Bible b WHERE chapter not between 29 to 35;

Answer 3

SELECT book FROM Bible WHERE chapter BETWEEN 29 AND 35;

Answer 4

If you do a Distinct and Genesis is returned, you have already satisfied the query conditions. Genesis has more than 29 and is returned. When you hit 30, you've already returned Genesis, so you only see it once. You want between or greater than.

SELECT b.book FROM Bible b WHERE chapter >= 29;

I'm not sure what you need to accomplish other than that or what you envision it looking like. Also see Chris Caviness comment.

EDIT:

SELECT b.book FROM Bible b WHERE chapter BETWEEN 29 AND 35;

Answer 5

The code seems to be fine. Could it be a problem with data types? What type is the column 'chapter' set to?