cristopher pierson ewing <[EMAIL PROTECTED]> wrote on 01/27/2005
04:01:22 PM:
> I'm running a query that pulls information from about six different
tables
> in a DB. I'd like to be able to do a fulltext search on fields in
several
> different tables. The end result should be that any row with a fulltext
> match in any of the fields in any table gets returned. I've tried a
> syntax that looks like this:
>
> WHERE MATCH (table1.field1,table2.field2 table2.field3)
> AGAINST ('some,nifty,words')
>
> but I get back an error message that says:
>
> ERROR 1210: Wrong arguments to MATCH
>
> If all the ffields are from one table, then I get an error that says:
>
> ERROR 1191: Can't find FULLTEXT index matching the column list
>
> Is it possible to do a fulltext search on multiple fields in a quesry
that
> references more than one table? What would be the correct syntax for
such
> a query? Am I limited to doing this via a UNION-type query?
>
> Thanks for any information that you can give me, and sorry if it seems a
> trivial question, I can't seem to find an answer in the documentation
>
> ********************************
> Cris Ewing
> CME and Telehealth Web Services
> University of Washington
> School of Medicine
> Work Phone: (206) 685-9116
> Home Phone: (206) 365-3413
> E-mail: [EMAIL PROTECTED]
> *******************************
>
>
I don't think you can define a single full-text index that spans multiple
tables. That would require the capacity to FT index a view. So I must
assume that you have created a FT index on one or more columns on each of
table1 and table2. If not, that may be your problem (you need to create a
FT index before you can use it).
It may be possible to say
SELECT...
FROM table1
INNER JOIN table2
ON ...
WHERE MATCH (table1.field1) AGAINST (...)
OR MATCH (table2.field2, table2.field3) AGAINST (...)
and get the results you want. I can't test it because I don't have any FT
indexes, yet.
Can you describe your FT index structure? ("SHOW CREATE TABLE xxxx\G"
creates great output for this purpose. Just edit out the fields that
aren't important to this problem if you are worried about size/secrets.)
That would go a long way to help us understand your problems.
Thanks!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
