I am trying to write a query using the fulltext and match and I've run into a problem.
I'm new to MySQL, so this question might have already been asked, but I just can't
seem to find it.
I'm using the fulltext capabilities to do a query. The query requires joins with four
different tables. The problem that I've run into is once I add the fourth table, the
match column no longer has any value -- it's always zero. I'm running version 3.23.32
on a Windows 2000 server machine.
Query 1: Works as expected
select message.thread_id, message.forum_id, message.user_id, message.created, match
message.body against ('test') as score,
thread.thread_title, user.username
from message,thread,user
where message.thread_id = thread.thread_id
and message.user_id=user.user_id
order by score desc limit 10
Query 2: Returns rows (the joins are working), but match column is zero for every
record.
The only difference is the addition of the forum table (adding forum.title as a
column, and adding a join condition in the where clause)
select message.thread_id, message.forum_id, message.user_id, message.created, match
message.body against ('test') as score,
thread.thread_title, user.username, forum.title
from message,thread,user,forum
where message.forum_id = forum.forum_id
and message.thread_id = thread.thread_id
and message.user_id=user.user_id
order by score desc limit 10
Am I missing the point with all this? Should I be doing a match...limit x into a
temporary table and then do my join against that?
I would appreciate any help anyone can offer.
