Select rows from a table that contain any word from a long list of words in another table

Question

I have one table with every Fortune 1000 company name:

FortuneList:

------------------------------------------------
|fid        |  coname                          |
------------------------------------------------
| 1         | 3m                               |
| 2         | Amazon                           |
| 3         | Bank of America                  |
| 999       | Xerox                            |
------------------------------------------------

I have a 2nd table with every user on my newsletter:
MyUsers:

------------------------------------------------
|uid    |  name      | companyname             |
------------------------------------------------
| 1350  | John Smith |  my own Co              |
| 2731  | Greg Jones |  Amazon.com, Inc        |
| 3899  | Mike Mars  |  Bank of America, Inc   |
| 6493  | Alex Smith |  Handyman America       |
------------------------------------------------

How do I pull out every one of my newsletter subscribers that works for a Fortune 1000 company? (By scanning my entire MyUsers table for every record that has any of the coname's from the FortuneList table)

I would want output to pull:

------------------------------------------------
|uid   |  name       | companyname             |
------------------------------------------------
| 2731  | Greg Jones |  Amazon.com, Inc        |
| 3899  | Mike Mars  |  Bank of America, Inc   |
------------------------------------------------

(See how it finds "Amazon" in the middle of "Amazon.com, Inc")

Answer 1

Try using this, which uses an INNER JOIN, the LIKE operator, and CONCAT:

SELECT *
FROM MyUsers
INNER JOIN FortuneList
    ON FortuneList.coname LIKE CONCAT('%', MyUsers.companyname, '%)

(This wouldn't use your Full Text index, I'm trying to figure out how you could use a MATCH...AGAINST in a JOIN.)

Answer 2

If you were doing this in Oracle, this would yield your desired result (with the example data):

with    fortunelist as(
        select 1 as fid, '3m' as coname from dual union all
        select 2, 'Amazon' from dual union all
        select 3, 'Bank of America' from dual union all
        select 999, 'Xerox' from dual
        )
        , myusers as(
        select 1350 as usrid, 'John Smith' as name, 'my own Co' as companyname from dual union all
        select 2731, 'Greg Jones', 'Amazon.com, Inc.' from dual union all
        select 3899, 'Mike Mars', 'Bank of America, Inc' from dual union all
        select 6493, 'Alex Smith', 'Handyman America' from dual 
        )
select  utl_match.jaro_winkler_similarity(myusers.companyname, fortunelist.coname) as sim
        , myusers.companyname
        , fortunelist.coname
from    fortunelist
        , myusers
where   utl_match.jaro_winkler_similarity(myusers.companyname, fortunelist.coname) >= 80

The reason being, the Jaro Winkler result for the 2 you're after are 87 and 95 (Amazon and BOA, respectively). You can bump the 80 in the query up or down to make the matching threshold higher or lower. The higher you go, the fewer matches you'll have, but the more likely they will be. The lower you go, the more matches you'll have, but you risk getting matches back that aren't really matches. For instance, "Handyman America" vs. "Bank of America" = 73/100. So if you lowered it to 70, you would get a false positive, using your example data. Jaro Winkler is generally meant for people's names, not company names, however because company names are typically also very short strings, it may still be useful for you.

I know you tagged this as MySQL and while this function does not exist, from what I've read people have already done the work creating a custom function for it: http://androidaddicted.wordpress.com/2010/06/01/jaro-winkler-sql-code/ http://dannykopping.com/blog/fuzzy-text-search-mysql-jaro-winkler

You could also try string replacements, ex. eliminating common reasons for a match not being found (such as there being an "Inc." on one table but not the other).

Edit 2/10/14:

You can do this in MySQL (via phpmyadmin) following these steps:

1. Go into phpmyadmin then your database and paste the code from this URL link (below) into a SQL window and hit Go. This will create the custom function that you'll need to use in Step 2. I'm not going to paste the code for the function here because it's long, also it's not my work. It basically allows you to use the jaro winkler algorithm in MySQL, the same way you would with utl_match if you were using Oracle. http://androidaddicted.wordpress.com/2010/06/01/jaro-winkler-sql-code/

2. After that function is created, run the following SQL:

-

select  jaro_winkler_similarity(myusers.companyname, fortunelist.coname) as similarity
        , myusers.uid
        , myusers.name
        , myusers.companyname as user_co
        , fortunelist.coname as matching_co
from    fortunelist
        , myusers
where   jaro_winkler_similarity(myusers.companyname, fortunelist.coname) >= 80

This should yield the exact result you're looking for, but like I said you'll want to play around with the 80 in that SQL and go up or down so that you have a good balance between avoiding false positives but also finding the matches that you want to find.

I don't have a MySQL database with which to test so if you run into an issue please let me know, but this should work.

Answer 3

Using LOCATE (no index thus):

select uid, name, companyname
from MyUsers JOIN FortuneList
WHERE LOCATE(coname, companyname) > 0