Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2004
    Posts
    494

    Unanswered: WHERE 4 of the 10 keywords available in records

    Is it possible to have several keywords in the search separated with an OR but only to show the records where there are ...let's say 4 of the 10 keywords available?
    Compare bible texts (and other tools):
    TheWheelofGod

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it is

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2004
    Posts
    494
    Ok ... how?
    Compare bible texts (and other tools):
    TheWheelofGod

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    with a GROUP BY and HAVING



    i will give more info, if you will give more info

    for starters, what is the primary key of the table, please?

    and how are the keywords related to the main table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2004
    Posts
    494
    Here's an example:
    SELECT * FROM bible WHERE 1=1 AND text_data LIKE '%there%' OR text_data LIKE '%house%' OR text_data LIKE '%daughter%' OR text_data LIKE '%woman%' OR text_data LIKE '%conceived%' OR text_data LIKE '%goodly%' OR text_data LIKE '%child%' OR text_data LIKE '%three%' OR text_data LIKE '%months%' LIMIT 0, 10
    The primary key is ID. The keywords are found in the text_area column.
    Compare bible texts (and other tools):
    TheWheelofGod

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT * 
      FROM bible 
     WHERE 1=1 
       AND CASE WHEN text_data LIKE '%there%' 
                THEN 1 ELSE 0 END
         + CASE WHEN text_data LIKE '%house%' 
                THEN 1 ELSE 0 END
         + CASE WHEN text_data LIKE '%daughter%' 
                THEN 1 ELSE 0 END
          > 1
    this example shows how to test to see if at least 2 of the 3 conditions have been met
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2004
    Posts
    494
    Quote Originally Posted by r937
    Code:
    SELECT * 
      FROM bible 
     WHERE 1=1 
       AND CASE WHEN text_data LIKE '%there%' 
                THEN 1 ELSE 0 END
         + CASE WHEN text_data LIKE '%house%' 
                THEN 1 ELSE 0 END
         + CASE WHEN text_data LIKE '%daughter%' 
                THEN 1 ELSE 0 END
          > 1
    this example shows how to test to see if at least 2 of the 3 conditions have been met
    how can I add another field outside of the CASES?
    Compare bible texts (and other tools):
    TheWheelofGod

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    outside of?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2004
    Posts
    494
    Quote Originally Posted by r937
    outside of?
    What I mean is,
    If I have this:
    Code:
    SELECT *
      FROM (
           SELECT bible.* 
                , CASE WHEN text_data LIKE '%there%' 
                       THEN 1 ELSE 0 END
                + CASE WHEN text_data LIKE '%house%' 
                       THEN 1 ELSE 0 END
                + CASE WHEN text_data LIKE '%daughter%' 
                       THEN 1 ELSE 0 END
                  AS relevance      
             FROM bible
            WHERE text_data LIKE '%there%'
               OR text_data LIKE '%house%'
               OR text_data LIKE '%daughter%'
          ) AS d 
     WHERE relevance > 4
    ORDER
        BY relevance
    Where am I going to fit "AND book_spoke = ' ' OR chapter_spoke=' ' OR verse_spoke = ' '"
    Compare bible texts (and other tools):
    TheWheelofGod

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT *
      FROM (
           SELECT bible.* 
                , CASE WHEN text_data LIKE '%there%' 
                       THEN 1 ELSE 0 END
                + CASE WHEN text_data LIKE '%house%' 
                       THEN 1 ELSE 0 END
                + CASE WHEN text_data LIKE '%daughter%' 
                       THEN 1 ELSE 0 END
                  AS relevance      
             FROM bible
            WHERE (
                  text_data LIKE '%there%'
               OR text_data LIKE '%house%'
               OR text_data LIKE '%daughter%'
                  )
              AND (
                  book_spoke = ' ' 
               OR chapter_spoke = ' ' 
               OR verse_spoke = ' ' 
                  )
          ) AS d 
     WHERE relevance > 4
    ORDER
        BY relevance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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