mysql MATCH to find best name matches in two fields

[br8kwall]

This seems to be a derivation of a question that I've seen quite a bit in my quest. Here it is: I'm trying to use the mysql MATCH function to identify the best matching names between two lists stored in two fields in two tables. I think this can be done, but my logic engine is failing me. The FULLTEXT index should be on the NAME fields.

master_table
-------------------------------
ID1, NAME1
1, mike the great
2, great peter the banana
3, john who shall go unamed
4, jim the stick in the woods

match_table
-------------------------------
ID2, NAME2
1, peter great
2, mike great
3, john unamed


desired result_table
-------------------------------
ID3, ID1, NAME2_BEST, SCORE
1, 1, mike great, 0.984
2, 2, peter great, 0.876
3, 3, john unamed, 0.735
4, 4, NULL

Can anyone suggest an approach here?

 

[br8kwall]

Ok, so I kinda answered my own question here. It seems the AGAINST() function can only accept constant values, and not something like fields. So, while I wanted a purely database operation here, I'll have to write a loop in someother language and iterate through the values in a field.

For this I'm going to do something like this, and iterate through the entire list, doing inserts as I go.

SELECT NAME, MATCH(NAME) AGAINST ('AGUA BAND INDIANS') AS SCORE
FROM LOGINS
WHERE MATCH (NAME) AGAINST('AGUA BAND INDIANS')
order by score desc limit 0,1;

This returns the single best match and its score