The implication of what you're saying is that you only want to return a match when all the words in the data are matched by all the words in the search string.
e.g. if you stored War and Peace in your db, then the user would have to enter a search term that included every single word found in War and Peace in order to match against it.
This seems an odd requirement.
The only way I can think of doing it is to count the words in the entry, and see if the score of the search matches (or is greater than) the number of words.
select *,
( LENGTH(mytext) - LENGTH(REPLACE(mytext, ' ', ''))+1) as WCnt,
match(mytext) against('sweet crusty pie' in boolean mode) as score
from text_search
where match(mytext) against('sweet crusty pie' in boolean mode)
and match(mytext) against('sweet crusty pie' in boolean mode)=( LENGTH(mytext) - LENGTH(REPLACE(mytext, ' ', ''))+1)
order by score desc;
The above, for example, will match against "sweet pie" since the score is 2 and the number of words in "sweet pie" is also 2.
Edited 1 time(s). Last edit at 09/10/2009 02:44AM by Tom Melly.