Page 1 of 4 123 ... LastLast
Results 1 to 15 of 58
  1. #1
    Join Date
    Jul 2004
    Posts
    494

    Unanswered: how to select a chapter of records which have keywords

    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.
    Compare bible texts (and other tools):
    TheWheelofGod

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are not clear in your request
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2004
    Posts
    494
    Ok. I have a database of a chapter field a verse field and a text field:
    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.
    Compare bible texts (and other tools):
    TheWheelofGod

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2004
    Posts
    494
    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.
    Compare bible texts (and other tools):
    TheWheelofGod

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2004
    Posts
    494
    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?
    Compare bible texts (and other tools):
    TheWheelofGod

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2004
    Posts
    494
    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.
    Compare bible texts (and other tools):
    TheWheelofGod

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jul 2004
    Posts
    494
    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).
    Compare bible texts (and other tools):
    TheWheelofGod

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
            )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jul 2004
    Posts
    494
    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.
    Compare bible texts (and other tools):
    TheWheelofGod

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jul 2004
    Posts
    494
    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.
    Last edited by gilgalbiblewhee; 09-23-08 at 14:47.
    Compare bible texts (and other tools):
    TheWheelofGod

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •