Actually, I didn't correctly type in the example. In the second example,
one field from the table forum is also included in the query.
Apparently there are problems with match() if MySQL uses can't do the entire
query without creating its own temporary tables. I've just rewritten it
like this:
create temporary table matches select message_id, message.thread_id,
message.forum_id, message.user_id, message.created, match message.body
against ('test') as score order by score as score limit 10
I then do another query against the matches table. So far, it hasn't
missed.
----- Original Message -----
From: "Bob Hall" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, February 04, 2001 12:48 PM
Subject: Re: Problem with Match / Against and multi-table join
> >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.
>
> Sir, in the example you're not doing anything with the fourth table.
> Why are you adding it to the FROM clause?
>
> Bob Hall
>
> Know thyself? Absurd direction!
> Bubbles bear no introspection. -Khushhal Khan Khatak
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
