mysqlmatchwhere-clauseagainst

MySQL MATCH AGAINST query with long WHERE AND OR syntax?

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!

Solution

  • 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.