Mysql: Using AGAINST() in a subquery

Posted on

Question :

I am doing a MATCH() and AGAINST() in my select in Order to COUNT data from another table quickly.

for a single string, I can do it with ease:

SELECT * from persons WHERE MATCH(full_name) AGAINST('"DOE, JOHN"');

Is it possible for AGAINST() to contain a column name as a parameter and use CONCAT() in order to produce a similar paramater AGAINST('"DOE, JOHN"');?

I have tried the query below but it’s a no go.

 SELECT id,
        full_name,
        citymun_id,
        (SELECT COUNT(*) from other_persons WHERE MATCH(other_persons .full_name) AGAINST(CONCAT('"',persons.full_name,'"')) as person_count
 FROM persons

Answer :

https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html says:

AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.

Leave a Reply

Your email address will not be published. Required fields are marked *