MySQL Search / compare Keywords in table 1 and Table 2 :

Question

Hey , i got this challenge , i got a MySQL DB table 1 with queries or text and table 2 with synonyms and misspellings as CSV [comma separated values]. Now i want to test if any query word in table 1 matches a synonym or misspelling in table 2 , then i would select them separately .

example :

table 1 row: "i am sick of HIV AIDS , what can i do?"

table 2 : HIV,AIDS,Cancer,TB,Chicken Pox ......

so this would be selected because at least there is a MATCH word in table 1 that matches a synonym in table 2.

Answer 1

select strings.text
  from table1 strings
 where exists (
         select 1
           from table2 sm
          where instr(strings.text, sm.word) <> 0
       )

Answer 2

On a MyISAM table:

SELECT  *
FROM    table1 com, table2 syn
WHERE   MATCH (com.body) AGAINST(syn.list IN BOOLEAN MODE);

This will work even if your don't have a FULLTEXT index on com.body, but with a FULLTEXT index this will be super fast.

If you wrap your synonym lists into double quotes, like this:

"HIV", "AIDS", "chicken pox", "swine flu"

, only the whole phrases will be matched, not just split words.