MySQL MATCH AGAINST query with long WHERE AND OR syntax?

Question

The following query is not working the way I expect:

SELECT DISTINCT * 
FROM mytable 
WHERE MATCH (StrNum, StrName, StrType, TownName, Zip) AGAINST ('elm') 
AND Color = 'RED' OR Color = 'WHITE' OR Color = 'BLUE'

This is returning more results than I expect - it's not limiting my results to those on 'elm'.

If I remove the last line (AND Color...), I can see that my MATCH AGAINST is working just fine and is indeed limiting to just those on 'elm'.

Do I need to do a subquery or something to pull of the Color stuff? Proper syntax would be really helpful, thanks!

Answer 1

Could this be written like so

SELECT DISTINCT * FROM mytable 
WHERE MATCH (StrNum, StrName, StrType, TownName, Zip) AGAINST ('elm')
AND Color IN ('RED', 'WHITE', 'BLUE') 

Hope this helps.

Answer 2

Maybe parentheses will help ?

SELECT DISTINCT * 
FROM mytable 
WHERE MATCH (StrNum, StrName, StrType, TownName, Zip) AGAINST ('elm') 
AND ( Color = 'RED' OR Color = 'WHITE' OR Color = 'BLUE' )

Answer 3

Using MATCH AGAINST requires atleast 4 characters for each word searched for (By default anyways) so 'Elm' would be ignored.