Solved

MySQL Fulltext Search with Multiple Tables

Posted on 2010-11-21
1
474 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
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 …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now