I've got a MySQL query somewhat like the following:
SELECT * FROM products LEFT JOIN descriptions ON products.DescriptionID = descriptions.ID WHERE MATCH (name, overview, specs) AGAINST ('ram');
All columns I'm trying to search with
MATCH() AGAINST() are FULLTEXT, but I get the following error when testing in phpMyAdmin:
#1210 - Incorrect arguments to MATCH
If I only
MATCH one column it doesn't error and works correctly, but as soon as I try to
MATCH multiple columns it fails with this error. I'm running MySQL 5.0.45 and the MySQL 5.0 Full-Text Search Functions documentation implies I can do this.
Is it because of the
LEFT JOIN? Do I need to
OR together a bunch of
MATCH() AGAINST() function calls?
Update @Zak: I can't post the table creation statements, but I can say that in this example the columns are as follows:
descriptions.specs. If I specify them in full
table.column format, it doesn't change the behavior.
However, if I lose
products.name from the
MATCH() I get the following error:
#1191 - Can't find FULLTEXT index matching the column list
descriptions.specs are FULLTEXT.
The arguments to
MATCH() must be the same columns in your FULLTEXT index definition. They must be the same in number as well as position.
Also, since you can't make an index with multiple columns from different tables, you can't match against columns from different tables in a single
If you want to search text columns from different tables, you'll have to create a FULLTEXT index in each table, and then search each index with a separate
MATCH() call. Use
OR expressions to combine the results.
I've run into that same problem. The thing is that you can't match columns from different tables so you have to split the query.
Try something like this (edited to match columns and tables):
SELECT p.name, d.overview, d.specs FROM products AS p LEFT JOIN descriptions AS d ON p.DescriptionID = d.ID WHERE MATCH (p.name) AGAINST ('ram') OR MATCH (d.overview, d.specs) AGAINST ('ram');
Hope it helps!