7259

MySQL full text search, why am I getting Incorrect arguments to MATCH

Question:

SELECT * FROM company c INNER JOIN city ci ON ci.city_id = c.city_id INNER JOIN state s ON s.state_id = c.state_id WHERE MATCH ( c.name, ci.name, c.zipcode, s.name ) AGAINST ( 'los angeles' )

Answer1:

All columns in MATCH (...) must be from the same table, because they must be fulltext indexed, and MySQL cannot create one index for data in multiple tables.

Answer2:

I had the same problem and solved it like this:

SELECT * FROM company c INNER JOIN city ci ON ci.city_id = c.city_id INNER JOIN state s ON s.state_id = c.state_id WHERE MATCH (c.name) AGAINST ('los angeles') OR MATCH (ci.name) AGAINST ('los angeles') OR MATCH (c.zipcode) AGAINST ('los angeles') OR MATCH (s.name) AGAINST ('los angeles')

But as I see, You are searching in simple fields like 'name' and 'zipcode'. As for me, it would be better to use LIKE and just concatenate them

SELECT * FROM company c INNER JOIN city ci ON ci.city_id = c.city_id INNER JOIN state s ON s.state_id = c.state_id WHERE CONCAT_WS (' ', c.name, ci.name, c.zipcode, s.name) LIKE ('%los angeles%')

Recommend

  • Handsontable Select2 Dependent Dropdowns
  • MS Access INNER JOIN most recent entry
  • How do I apply this Perl expression to each line of the file? [closed]
  • Need a mod_rewrite .htaccess solution to replace %20 spaces with -'s in the finished URL
  • non-negative integer solutions to system of linear equations in mathematica
  • How to Stop a SwingWorker?
  • The target “TransformAll” does not exist in the project T4 transformation MSBuild
  • JavaScript onDeviceReady not firing
  • How to render shape to string?
  • Bing Virtual Earth 7.0 calculate area
  • Jquery autocomplete with php as remote source: how to append second variable from input to source
  • Insert multiple values into hidden field
  • Relational Database Mapping in MVC
  • Add delivery info to query in SAP Crystal Reports
  • Python: Split a String Field into 3 Separate Fields using Lambda
  • end daemon processes with multiprocessing module
  • Does the MySQL IN clause execute the subquery multiple times?
  • d3.js selection conditional rendering
  • Use default value of a column in stored procedures
  • Getting short path in python
  • joining two bezier curves
  • How Get arguments value using inline assembly in C without Glibc?
  • Basic many-to-many left join query
  • How to make R's read_csv2() recognise the text characters properly
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • Implicit joins and Where in Doctrine - how?
  • Implementation of State Monad
  • D3 get axis values on zoom event
  • Breaking out column by groups in Pandas
  • Unable to get column index with table.getColumn method using custom table Model
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • How do I pass the string value parameter of the selected list item from an auto-populated dropdown l
  • Extracting HTML between tags
  • Jetty Server not starting: Unable to establish loopback connection
  • Django: Count of Group Elements
  • MongoDB in PHP using aggregate to group by _id is null not working
  • Why HTML5 Canvas with a larger size stretch a drawn line?
  • How to check if every primary key value is being referenced as foreign key in another table
  • How to handle AllServersUnavailable Exception
  • How to get next/previous record number?