Rows where some of columnA exists in columnB

Databases
1

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.

Thanks!

2

Well, depending on your data and the results you actually want, there are a couple of things you can look at.

You may want to take a look at using JOIN in your SELECT queries.

This article offers a pretty good explanation on what it is and how to use it: http://www.sitepoint.com/understanding-sql-joins-mysql-database/

For something a bit more technical, the official documentation: http://dev.mysql.com/doc/refman/5.0/en/join.html

Also, you may need to look at using LIKE instead of = for string comparisons.

http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html

3

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.

4

except there was no hint of more than one table in this problem :slight_smile:

5

Correct, there is only one table involved here, the searches table. So is this kind of query even possible?

6

yes, but it will be ugly as sin

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),'%')
    ...
7

Ok, this is what I got:

Incorrect parameter count in the call to native function ‘SUBSTRING_INDEX’

Thank you.

8

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

:slight_smile: