MySQL可选LEFT JOIN与MATCH [英] MySQL Optional LEFT JOIN With MATCH

问题描述

我有以下查询，它针对MySQL Innodb数据库中的相同搜索词对两个不同表中的两列执行全文搜索;

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 

如果我只在tb1.comment上执行MATCH，它可以正常工作，并且返回相关搜索条款，但我想对这两列执行它。

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.

然而，因为另一个表是可选的，所以LEFT JOIN不会返回任何东西，如果没有匹配IDS。任何想法如何克服这个问题？

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 ?

推荐答案

我设法弄清楚下面的工作，似乎表现良好，给我想要的结果;

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

这篇关于MySQL可选LEFT JOIN与MATCH的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！