I am trying to do a match querying from two tables:

SELECT a.id,a.forum,a.topic,b.topic_id,b.body MATCH(a.topic,b.body) AGAINST('guitar,microphone') AS relevance FROM forum_topics a INNER JOIN forum_threads b ON a.id=b.topic_id WHERE MATCH(a.topic,b.body) AGAINST('guitar,microphone') ORDER BY relevance DESC LIMIT 0,10

What is wrong with that? Is a dual table match possible? I have a couple single table queries working great, Ihave full text indexing on a.topic and b.body...I am confused

Thanks for any help.

    AGAINST('guitar,microphone') --> AGAINST('guitar','microphone')

      Thanks for the response!

      OK, I can accept that little error, but then why doesn't this work either:

      SELECT a.id,a.forum,a.topic,b.topic_id,b.body MATCH(a.topic,b.body) AGAINST('guitar') AS relevance FROM forum_topics a INNER JOIN forum_threads b ON a.id=b.topic_id WHERE MATCH(a.topic,b.body) AGAINST('guitar') ORDER BY relevance DESC LIMIT 0,10

      ? Any thoughts? And doing it the way I stated in my previous code works very well on my single table queries. Are you SURE that is proper syntax, I read otherwise at some article, but now I can't find it again...

        you need ',' in, your select clause before MATCH

        SELECT a.id,a.forum,a.topic,b.topic_id,b.body, MATCH(a.topic,b.body) AGAINST('guitar') AS relevance FROM forum_topics a INNER JOIN forum_threads b ON a.id=b.topic_id WHERE MATCH(a.topic,b.body) AGAINST('guitar') ORDER BY relevance DESC LIMIT 0,10 ,

          Ahhhhhh, there we go, I will give that a try later on, thanks!

            Well, I figured it out, apparantly you can't do a match on columns from two different tables, or column in different indexes. At least not in v3.

              Write a Reply...