[SOLVED] MySQL Full Text Search not working with left join

MySQL Full Text Search not working with left join

I am using full text search on a table , and left joining it with a table from a different DB. The table (table1) has a full-text index defined on the column name.

when I try using full text search the following query DOES NOT WORK:

SELECT t1.id, t2.id, t2.url
FROM `db1`.table1 t1
LEFT JOIN `db2`.table2 t2
ON t1.column1 = t2.column1
WHERE t2.column1 IS NOT NULL
AND MATCH (t1.name) AGAINST ('searchString' IN NATURAL LANGUAGE MODE)

but when using it without the left join the query WORKS:

SELECT t1.id
FROM `db1`.table1 t1
WHERE MATCH (t1.name) AGAINST ('searchString' IN NATURAL LANGUAGE MODE)

NOTE : I cannot use LIKE %searchString%, instead of match-against for performance issues.

Is there a way of using full text search and left join in the same query?

EDIT: it seems that the problem is somehow related to the fact that both tables are not in the same db. when trying to do the same query with two tables from the same db, it works. any idea why this might happen?

Solution

Yes We can use Full text search and left join in the same query.

Check example and query. : Link

        SELECT t1.id, t2.id, t2.url
        FROM `db1`.table1 t1
        LEFT JOIN `db2`.table2 t2
        ON t1.column1 = t2.column1 and t2.column1 IS NOT NULL
        where MATCH (t1.name) AGAINST ('searchString' IN NATURAL LANGUAGE MODE)

Please let us know, if there in any error.