I have a MySQL database for searching songs that has two tables, a song table and a keywords table. The keywords are indexed to the song, and so each song can have a bunch of keywords.

I want to be able to search the songs with boolean searching (i.e. "guitar -rock" would find non-rock guitar music). I am doing a fulltext boolean match to keywords and returning the distinct song values. The trouble with this method, is that while it matches perfectly for positive values, it does not exclude songs with a negated value.

So, for a simplified example, I would be doing:

SELECT DISTINCT song 
FROM keywords 
WHERE (MATCH (keyword_text) AGAINST ('guitar -rock' IN BOOLEAN MODE))

Is there a way, through a MySQL query, that will handle the negation?

If worse comes to worse, I can always have a batch script that will create a table "song_keywords", and simply combine each keyword into a single text field and boolean search that, but I would prefer to avoid creating another table with duplicate data and script overhead just for that. It seems to me there has to be a better way, a way that can be done 100% through queries... but I am missing it!

Does anyone have any insight?