Solved

MySQL Fulltext Search with Multiple Tables

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Transferring database from one server to another 1 65
showing numeric numbers 2 33
Amazon Redshift 2 27
MySQL Error Code 2 0
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 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

14 Experts available now in Live!

Get 1:1 Help Now