I tried looking on the web for an answer but could not find anything so I will ask you guys. Is it possible to write a query that returns rows where at least one word in columnA is also found in columnB? Basically the keywords column shouldn’t contain any words that exist in the body column. Those are the rows I’m wanting to find.
So the keywords column is a comma separated value column (keyword1, keyword2, keyword3, …) ? That’s too bad. If you had a “keywords” table with one articleid/keyword pair per row (supposing your table is called “articles”), then it would be easy to do what you want.
you’ll have to hardcode as many conditions as there are keywords you want to check
each one will use a SUBSTRING_INDEX function to pull out a keyword, and then LIKE to see if it’s in the body
SELECT ...
FROM searches
WHERE body LIKE CONCAT('%',SUBSTRING_INDEX(keywords,1),'%')
OR body LIKE CONCAT('%',SUBSTRING_INDEX(SUBSTRING_INDEX(keywords,2),-1),'%')
OR body LIKE CONCAT('%',SUBSTRING_INDEX(SUBSTRING_INDEX(keywords,3),-1),'%')
...
obviously, the sql i gave you was untested, in fact it was right off the top of my head, and i’m sorry, but i guess i mis-remembered the function syntax
do me a favour please?
look up SUBSTRING_INDEX in da manual and you will see immediately what’s wrong