Interesting Question on MATCH () AGAINST

[dealmaker]

Hi,
I am trying to writing a sql query that matches 2 tables consisted of rows of keywords. The keywords can have minus sign in front of them, and it means keywords I don't want to match.

for examples:

a. table 1 row 1 keywords: hello good morning -afternoon
b. table 2 row 1 keywords: hello good morning
c. table 2 row 2 keywords: hello good afternoon
d. table 2 row 3 keywords: hello good -morning

so, a matches b, and but not d and c.

How can I do that? AGAINST () take words only, not columns.

[AaronW]

Would it make more sense to just list keywords normally, but add a WHERE clause for each word with a - in the query?

So if I'm looking for "hello good -morning -bye" the query is:

SELECT MATCH () AGAINST ... table WHERE field NOT LIKE '%morning%' AND field NOT LIKE '%bye%'

?

Or maybe you've got some other functionality planned... Bleh.

[dealmaker]

I think that problem is that all these keywords are stored inside 2 tables. So, it's like using table to match table instead of using words to match table.

for examples, I have 2 tables. Both tables have all the people who love and hate certain color. There are 2 tables because there are 2 kinds of people and they have different columns for other info besides color which doesn't concern us here. So, I am trying to match both tables where people love the same color, take in account that people in table 1 (or table 2)don't dislike a color that people in table2 (or table 1)like.


Would it make more sense to just list keywords normally, but add a WHERE clause for each word with a - in the query?

So if I'm looking for "hello good -morning -bye" the query is:

SELECT MATCH () AGAINST ... table WHERE field NOT LIKE '%morning%' AND field NOT LIKE '%bye%'

?

Or maybe you've got some other functionality planned... Bleh.