MySQL Optional LEFT JOIN With MATCH

Question

I have the following query, which performs a full text search against two columns in two different tables for the same search term in a MySQL Innodb database;

SELECT Id, 
MATCH (tb1.comment, tb2.comment) AGAINST (+'search term' IN BOOLEAN MODE) AS Relevance
FROM tbl1
LEFT JOIN tb2 ON tb1.Id = tb2.Id
WHERE MATCH (tb1.comment, tb2.comment) AGAINST (+'search term' IN BOOLEAN MODE) 
HAVING Relevance > 0 

If I perform the MATCH on just tb1.comment it works fine and I get back the relevant search terms, but I want to perform it against both columns.

However because the other table is optional with the LEFT JOIN it doesn't return anything, if there is no matching Ids. Any ideas on how to overcome this problem ?

Answer 1

I managed to figure out the following work around that appears to perform fine and give the results I desire;

    SELECT Id, 
    MATCH (tb1.comment) AGAINST (+'search term' IN BOOLEAN MODE) AS Relevance1,
    MATCH (tb2.comment) AGAINST (+'search term' IN BOOLEAN MODE) AS Relevance2
    FROM tbl1
    LEFT JOIN tb2 ON tb1.Id = tb2.Id
    WHERE (MATCH (tb1.comment) AGAINST (+'search term' IN BOOLEAN MODE) 
    OR MATCH ( tb2.comment) AGAINST (+'search term' IN BOOLEAN MODE))
    HAVING (Relevance1+Relevance2) > 0 
    ORDER BY (Relevance1+Relevance2) DESC