WHERE 4 of the 10 keywords available in records

gilgalbiblewhee · #1 (**permalink**) 06-24-08, 17:10

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?

r937 · #2 (**permalink**) 06-24-08, 17:31

yes, it is

gilgalbiblewhee · #3 (**permalink**) 06-24-08, 18:03

Ok ... how?

r937 · #4 (**permalink**) 06-24-08, 18:21

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?

gilgalbiblewhee · #5 (**permalink**) 06-24-08, 23:15

Here's an example:

Quote:

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.

r937 · #6 (**permalink**) 06-24-08, 23:27

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

gilgalbiblewhee · #7 (**permalink**) 08-22-08, 19:57

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?

r937 · #8 (**permalink**) 08-22-08, 19:58

outside of?

gilgalbiblewhee · #9 (**permalink**) 08-22-08, 20:07

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 = ' '"

r937 · #10 (**permalink**) 08-23-08, 01:03

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

Internet Services

Web Development

Digital Marketing

Consumer Tech