Solved

MySQL Fulltext Search with Multiple Tables

Posted on 2010-11-21
1
481 Views
Last Modified: 2012-05-10
Hi, I'm trying to write a search query. I'm trying to take advantage of the "Natural Language" search capability.

Here is my query:

SELECT person.personid, person.firstname, person.lastname, facility.name, state.stateLongName, detail.description
FROM person
LEFT JOIN facility ON person.facility = facility.facilityid
LEFT JOIN detail ON person.personid = detail.personid
LEFT JOIN state ON facility.state = state.stateID
WHERE
MATCH (person.firstname, person.lastname, facility.name, state.stateLongName, detail.description)
AGAINST ('Texas')


I have FULLTEXT indexes set up on each of the columns in the MATCH part of the query.

I get the following error:
#1210 - Incorrect arguments to MATCH

Any thoughts on what I'm doing wrong?  I'm using MySQL 5.0.

0
Comment
Question by:ipeng
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34183983
you will need to do like this, using either AND or OR, depends on what exactly you need to be the results.
I post with OR, you can change as needed:
SELECT person.personid, person.firstname, person.lastname, facility.name, state.stateLongName, detail.description
FROM person
LEFT JOIN facility ON person.facility = facility.facilityid
LEFT JOIN detail ON person.personid = detail.personid
LEFT JOIN state ON facility.state = state.stateID
WHERE MATCH (person.firstname, person.lastname) AGAINST ('Texas')
   OR MATCH (facility.name) AGAINST ('Texas')
   OR MATCH (state.stateLongName) AGAINST ('Texas')
   OR MATCH (detail.description) AGAINST ('Texas')

Open in new window

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question