LEFT JOIN一个字符串(LEFT JOIN of a string)

LEFT JOIN一个字符串(LEFT JOIN of a string)

在我的网站上，用户输入多个搜索字词。 这些搜索词中的每一个都可以返回0行，或者返回几行。 我在所有搜索上执行UNION并获取行。

如果我可以在响应中获得所有搜索项，那么对我来说更容易，无论它们是否返回任何行。 有没有办法基本上LEFT JOIN一个字符串（在这种情况下是搜索项）到SELECT查询？

更多信息更新：

我有一个表与字段标题的书。

用户最多输入10个标题search_terms，我目前在所有这些上执行UNION以获得匹配的行。 就这样

SELECT $s[0] AS Search_Term,* WHERE Title LIKE '%$s[0]%' UNION SELECT $s[1] AS Search_Term,* WHERE Title LIKE '%$s[1]%' ...etc

我希望如果没有针对给定搜索的结果，我仍然会将Search_Term与NULL标题一起返回。

On part of my site, users enter multiple search terms. Each of these search terms may return 0 rows, or return several. I perform a UNION on all the searches and get the rows.

Thing are much easier for me if I can get all the search terms in the response, regardless of whether they return any rows. Is there a way to essentially LEFT JOIN a string (in this case the search term) to a SELECT query?

More information update:

I have a table Books with the field Title.

The user enters up to 10 title search_terms, and I currently perform a UNION on all these to get matching rows. So it's like

SELECT $s[0] AS Search_Term,* WHERE Title LIKE '%$s[0]%' UNION SELECT $s[1] AS Search_Term,* WHERE Title LIKE '%$s[1]%' ...etc

I'd like it so that if there are no results for a given search, I still get the Search_Term back along with the NULL Title.

最满意答案

如果将数组值连接在一起，可以使用这种方式使用全文搜索（FTS）：

SELECT x.searchterm, b.* FROM (SELECT $s[0 ... 20] AS search_term) x LEFT JOIN BOOKS b ON MATCHES(b.title) AGAINST (x.searchterm)

$s[0 ... 20]是一个占位符，用于连接成单个字符串的搜索词，用空格字符分隔每个词。

如果没有匹配项，LEFT JOIN将确保您获得搜索项和其余列的NULL。

You could use Full Text Search (FTS) this way, if you concatenate the array values together:

SELECT x.searchterm, b.* FROM (SELECT $s[0 ... 20] AS search_term) x LEFT JOIN BOOKS b ON MATCHES(b.title) AGAINST (x.searchterm)

$s[0 ... 20] is a placeholder for the search terms concatenated into a single string, separating each term with a space character.

The LEFT JOIN will ensure that you get your search term and NULL for the rest of the columns if nothing matches.