php - MYSQL PHP 搜索功能 INNER JOIN 不需要

Question

我有一个搜索查询:

SELECT a.id, 
       a.name, 
       i.image_id, 
       MATCH(a.name, a.about, a.tags) AGAINST ('%s' IN BOOLEAN MODE) AS relevance 
FROM table_1 a 
INNER JOIN image_table i ON a.id = i.field_id 
WHERE MATCH(a.name, a.about, a.tags) AGAINST ('%s' IN BOOLEAN MODE)!=0 
ORDER BY relevance DESC

这个查询的输出非常简单，它检索与字段名称、关于和标签匹配的元组。输出还使用 INNER JOIN 从另一个表获取相关图像。

但是，如果没有图像，即如果用户没有上传图像(将其插入图像表)，则整个字段不会被标记为可行匹配并作为结果返回。

How can I change this query to get the tuple from table_1 even if the image does not exist?

因为我可以为匹配的元组生成默认图像。

提前致谢。

Answer 1

使用左连接

SELECT a.id,        a.name,        i.image_id,        MATCH(a.name, a.about, a.tags) AGAINST ('%s' IN BOOLEAN MODE) AS relevanceFROM table_1 a LEFT JOIN image_table i ON a.id = i.field_id WHERE MATCH(a.name, a.about, a.tags) AGAINST ('%s' IN BOOLEAN MODE)!=0 ORDER BY relevance DESC