如何跨多个表使用全文搜索，SQL Server 2005 [英] How do I use full text search across multiple tables, SQL Server 2005

问题描述

我有一个包含两个表格的全文目录。

tableA有4列（a1，a2，a3，a4）目录，a2，a3，a4。
a1是主键。

tableB有3列（b1，b2，b3，b4），其中两列在目录中编入索引，b3和B4。 b1是此表的PK，b2是表A的FK。

我想要做一些类似于

  SELECT *，（ftTableA。[RANK] + ftTableB。[RANK]）AS total_rank 
 FROM tableA 
 INNER JOIN tableB ON tableA.a1 = tableB.b2 
 INNER JOIN FREETEXTTABLE（tableA，（a2，a3，a4），'search term'）as ftTableA ON tableA.a1 = ftTableA。[KEY] 
 INNER JOIN FREETEXTTABLE（tableB，（b3，b4）， 'search term'）as ftTableB ON tableB.11 = ftTableB。[KEY] 
  

但这并不工作...
我可以得到一个单独的表，例如。

$ $ p $ SELECT *，（ftTableA。[ RANK] + ftTableB。[RANK]）AS total_rank
FROM tableA
INNER JOIN FREETEXTTABLE（tableA，（a2，a3，a4），'search term'）as ftTableA ON tableA.a1 = ftTableA。[ KEY]


但从未超过一张表。

有人可以给出一个解释和/或示例，说明在多个表格上进行全文搜索所需的步骤。

您不说明什么

为什么不选择LEFT OUTER加入全文搜索，并替换：

  SELECT *，（ISNULL（ftTableA。[RANK]，0）+ ISNULL（ftTableB。[RANK]，0））AS total_rank 
  

和

  WHERE ftTableA.Key IS NOT NULL或ftTableB.Key IS NOT NULL 
  

I have a full text catalog with two tables in it.

tableA has 4 columns (a1, a2, a3, a4) of wich 3 are indexed in the catalog, a2,a3,a4. a1 is the primary key.

tableB has 3 columns (b1, b2, b3, b4), two of which are indexed in the catalog, b3 and b4. b1 is the PK of this table, b2 is the FK to tableA.

I want to do something like

SELECT *, (ftTableA.[RANK] + ftTableB.[RANK]) AS total_rank 
FROM tableA
INNER JOIN tableB ON tableA.a1=tableB.b2
INNER JOIN FREETEXTTABLE(tableA, (a2,a3,a4), 'search term') as ftTableA ON tableA.a1=ftTableA.[KEY]
INNER JOIN FREETEXTTABLE(tableB, (b3,b4), 'search term') as ftTableB ON tableB.11=ftTableB.[KEY]

But this does not work... I can get a single table to work, eg.

SELECT *, (ftTableA.[RANK] + ftTableB.[RANK]) AS total_rank 
FROM tableA
INNER JOIN FREETEXTTABLE(tableA, (a2,a3,a4), 'search term') as ftTableA ON tableA.a1=ftTableA.[KEY]

but never more than one table.

Could someone give an explanation and/or example of the steps required to full-text search over multiple tables.

解决方案

Your query only returns records, if both A and related B contains the search text.

You do not state what does not work, though.

Why not LEFT OUTER JOIN the fulltext searches, and replace:

SELECT *, (ISNULL(ftTableA.[RANK], 0) + ISNULL(ftTableB.[RANK], 0)) AS total_rank 

and

WHERE ftTableA.Key IS NOT NULL OR ftTableB.Key IS NOT NULL

这篇关于如何跨多个表使用全文搜索，SQL Server 2005的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！