how to select a chapter of records which have keywords

gilgalbiblewhee · #1 (**permalink**) 09-20-08, 06:35

Since a chapter has many records, I want to select a chapter which has the keywords. It may be that one record has one of the keywords and the next one has another. Let me know if I'm not clear in my request. Thanks.

r937 · #2 (**permalink**) 09-20-08, 06:57

you are not clear in your request

gilgalbiblewhee · #3 (**permalink**) 09-20-08, 12:42

Ok. I have a database of a chapter field a verse field and a text field:

Quote:

Chapter Verse Text_data
1 1 blablabasasda
1 2 asdkjsdfkdjhf
1 3 sdcj lj sdlckj
1 4 oin oihh o tsts
1 5 sdfoijd sdjfosd
1 6 nalsknd asdsad
2 1 alaajaj ajajk dodf
2 2 blablabasasda

by looking at the text data, let's say I'm looking for the keywords:
blablabasasda and sdcj.
Looking at chapter 2 blablabasasda is found but not sdcj. So that chapter is disregarded. But in chapter 1 since words keywords are found then Chapter 1 is taken into account.

r937 · #4 (**permalink**) 09-20-08, 14:10

Code:

SELECT Chapter 
  FROM daTable
 WHERE Text_data IN ( 'blablabasasda','sdcj' )
GROUP
    BY Chapter
HAVING SUM(CASE WHEN Text_data = 'blablabasasda'
                THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN Text_data = 'sdcj'
                THEN 1 ELSE 0 END) > 0

gilgalbiblewhee · #5 (**permalink**) 09-22-08, 22:18

Code:

Select * FROM book WHERE 1=1 AND text_data IN ( '%john%', '%cat%', '%dog%' ) GROUP BY chapter HAVING SUM(CASE WHEN text_data LIKE '%john%' THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN text_data LIKE '%cat%' THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN text_data LIKE '%dog%' THEN 1 ELSE 0 END) > 0

This select statement isn't printing any results.

r937 · #6 (**permalink**) 09-22-08, 22:46

you will make things a lot easier for yourself if you use line breaks and indentation

SQL should be written for human readers, not strung out on a single, humungously long line

change this --

Code:

SELECT * 
  FROM book 
 WHERE 1=1 
   AND text_data IN ( '%john%', '%cat%', '%dog%' ) 
GROUP 
    BY chapter 
HAVING SUM(CASE WHEN text_data LIKE '%john%' 
                THEN 1 ELSE 0 END) > 0 
   AND SUM(CASE WHEN text_data LIKE '%cat%' 
                THEN 1 ELSE 0 END) > 0 
   AND SUM(CASE WHEN text_data LIKE '%dog%' 
                THEN 1 ELSE 0 END) > 0

to this --

Code:

SELECT * 
  FROM book 
 WHERE 1=1 
   AND (
       text_data LIKE '%john%'
    OR text_data LIKE '%cat%'
    OR text_data LIKE '%dog%' 
       ) 
GROUP 
    BY chapter 
HAVING SUM(CASE WHEN text_data LIKE '%john%' 
                THEN 1 ELSE 0 END) > 0 
   AND SUM(CASE WHEN text_data LIKE '%cat%' 
                THEN 1 ELSE 0 END) > 0 
   AND SUM(CASE WHEN text_data LIKE '%dog%' 
                THEN 1 ELSE 0 END) > 0

gilgalbiblewhee · #7 (**permalink**) 09-22-08, 23:21

Quote:

Originally Posted by r937

you will make things a lot easier for yourself if you use line breaks and indentation

SQL should be written for human readers, not strung out on a single, humungously long line

change this --

Code:

SELECT * 
  FROM book 
 WHERE 1=1 
   AND text_data IN ( '%john%', '%cat%', '%dog%' ) 
GROUP 
    BY chapter 
HAVING SUM(CASE WHEN text_data LIKE '%john%' 
                THEN 1 ELSE 0 END) > 0 
   AND SUM(CASE WHEN text_data LIKE '%cat%' 
                THEN 1 ELSE 0 END) > 0 
   AND SUM(CASE WHEN text_data LIKE '%dog%' 
                THEN 1 ELSE 0 END) > 0

to this --

Code:

SELECT * 
  FROM book 
 WHERE 1=1 
   AND (
       text_data LIKE '%john%'
    OR text_data LIKE '%cat%'
    OR text_data LIKE '%dog%' 
       ) 
GROUP 
    BY chapter 
HAVING SUM(CASE WHEN text_data LIKE '%john%' 
                THEN 1 ELSE 0 END) > 0 
   AND SUM(CASE WHEN text_data LIKE '%cat%' 
                THEN 1 ELSE 0 END) > 0 
   AND SUM(CASE WHEN text_data LIKE '%dog%' 
                THEN 1 ELSE 0 END) > 0

ok. But does the * work with the statement? ...and the 1=1?

r937 · #8 (**permalink**) 09-22-08, 23:38

the dreaded, evil "select star" should never be used, especially with GROUP BY

it will "work" (execute) but it might not "work" (produce definitive results)

the 1=1 will always work

gilgalbiblewhee · #9 (**permalink**) 09-22-08, 23:54

Quote:

Originally Posted by r937

the dreaded, evil "select star" should never be used, especially with GROUP BY

it will "work" (execute) but it might not "work" (produce definitive results)

the 1=1 will always work ;)

Yeah what happens is that the OR in:

Code:

SELECT * 
  FROM book 
 WHERE 1=1 
   AND (
       text_data LIKE '%john%'
    OR text_data LIKE '%cat%'
    OR text_data LIKE '%dog%' 
       ) 
GROUP 
    BY chapter 
HAVING SUM(CASE WHEN text_data LIKE '%john%' 
                THEN 1 ELSE 0 END) > 0 
   AND SUM(CASE WHEN text_data LIKE '%cat%' 
                THEN 1 ELSE 0 END) > 0 
   AND SUM(CASE WHEN text_data LIKE '%dog%' 
                THEN 1 ELSE 0 END) > 0

is showing one of the keywords only in the result. But if I switch it to AND it shows all the keywords but per record and not per chapter of records.

r937 · #10 (**permalink**) 09-23-08, 00:12

why not go back to selecting only the Chapter as you originally required

that's what i meant about producing definitive results

if you GROUP BY Chapter, than you should SELECT only Chapter

gilgalbiblewhee · #11 (**permalink**) 09-23-08, 00:27

Quote:

Originally Posted by r937

why not go back to selecting only the Chapter as you originally required

that's what i meant about producing definitive results

if you GROUP BY Chapter, than you should SELECT only Chapter

By selecting chapter only the chapter number appears in the results and it shows only one of the keywords (when I added text_data to the select).

r937 · #12 (**permalink**) 09-23-08, 06:24

feels like we're going around in circles

i would refer you to posts #3 and #4 in this thread

you want to select specific chapters which have given text somewhere in the verses

yes, only chapter number appears in results

if you want to return chapter, verse, and text, then do this --

Code:

SELECT chapter, verse, text_data
  FROM book
 WHERE chapter IN (
        query from post #4 goes here
        )

gilgalbiblewhee · #13 (**permalink**) 09-23-08, 12:56

Quote:

Originally Posted by r937

feels like we're going around in circles

i would refer you to posts #3 and #4 in this thread

you want to select specific chapters which have given text somewhere in the verses

yes, only chapter number appears in results

if you want to return chapter, verse, and text, then do this --

Code:

SELECT chapter, verse, text_data
  FROM book
 WHERE chapter IN (
        query from post #4 goes here
        )

Unless it finds all the keywords in the chapter it shouldn't be in the results. So far that didn't turn out.

r937 · #14 (**permalink**) 09-23-08, 12:59

Quote:

Originally Posted by gilgalbiblewhee

Unless it finds all the keywords in the chapter it shouldn't be in the results.

that's what the HAVING clause in post #4 does

did you not test it?

gilgalbiblewhee · #15 (**permalink**) 09-23-08, 13:30

Quote:

Originally Posted by r937

that's what the HAVING clause in post #4 does
did you not test it?

Code:

Select book_title, chapter, verse, text_data FROM bible WHERE 1=1 AND ( 
text_data LIKE '%james%' OR 
text_data LIKE '%john%' OR 
text_data LIKE '%andrew%' ) 
GROUP BY chapter HAVING 
SUM(CASE WHEN text_data LIKE '%james%' 
     THEN 1 ELSE 0 END) > 0 AND 
SUM(CASE WHEN text_data LIKE '%john%' 
     THEN 1 ELSE 0 END) > 0 AND 
SUM(CASE WHEN text_data LIKE '%andrew%' 
     THEN 1 ELSE 0 END) > 0

But the result I get is as if it was written:

Code:

Select book_title, chapter, verse, text_data FROM bible 
WHERE 1=1 AND ( 
text_data LIKE '%james%' OR 
text_data LIKE '%john%' OR 
text_data LIKE '%andrew%' )

On the other hand:

Code:

Select book_title, chapter, verse, text_data FROM bible WHERE 1=1 AND 
text_data IN ( '%james%', '%john%', '%andrew%' ) 
GROUP BY chapter HAVING 
SUM(CASE WHEN text_data LIKE '%james%' THEN 1 ELSE 0 END) > 0 AND 
SUM(CASE WHEN text_data LIKE '%john%' THEN 1 ELSE 0 END) > 0 AND 
SUM(CASE WHEN text_data LIKE '%andrew%' THEN 1 ELSE 0 END) > 0

brings no results at all. Probably because the select statement has a contradiction within it. And having 'chapter' by itself is the same...no results.

Internet Services

Web Development

Digital Marketing

Consumer Tech