如何在多个表SQL Server 2005中使用全文搜索

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.

tableA在目录中索引了4列(a1,a2,a3,a4),a2,a3,a4。 a1是主键。

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.

tableB有3列(b1,b2,b3,b4),其中两列在目录中被索引,b3和b4。 b1是该表的PK,b2是表A的FK。

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.

有人可以给出对多个表进行全文搜索所需步骤的解释和/或示例。

2 个解决方案

#1

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

如果A和相关B都包含搜索文本,则查询仅返回记录。

You do not state what does not work, though.

但是,您没有说明什么不起作用。

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

为什么不LEFT OUTER JOIN全文搜索,并替换:

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

#2

I'm not positive that I understood what you were trying to do. I interpreted your question as you want to return all items in Table A that matched the search term. Furthermore you wanted to sum the rank from the item in TableA plus the matching items in TableB.

我并不认为我理解你要做的事情。我解释了您的问题,因为您想要返回表A中与搜索词匹配的所有项目。此外,您希望将TableA中项目的排名加上TableB中的匹配项目。

The best way I can think to do this is to use a table variable with 3 queries.

我能想到的最好方法是使用带有3个查询的表变量。

DECLARE @Results Table (a1 Int UNIQUE, Rank Int)

--Insert into @Results all matching items from TableA
INSERT INTO @Results
(a1, Rank)
( SELECT TableA.a1, FT.Rank
FROM TableA INNER JOIN FreeTextTable(TableA, *, 'search term') FT
ON TableA.A1 = FT.[Key]
)

--Update all of the ranks in @Results with a sum of current value plus the sum of
--all sub items (in TableB)
UPDATE @Results
SET Rank = RS.Rank + FT.Rank
FROM @Results RS INNER JOIN TableB
ON RS.A1 = TableB.b2
INNER JOIN FreeTextTable(TableB, *, 'search term') FT
ON TableB.b1 = FT.[Key]

--Now insert into @Results any items that has a match in TableB but not in TableA
--This query may/may not be desired based on your business rules.
INSERT INTO @Results
(SkillKeyId, Rank)
( SELECT TableB.b2, Sum(FT.Rank)
FROM TableB INNER JOIN FreeTextTable(TableB, *, 'search term') FT
ON TableB.b1 = FT.[key]
LEFT JOIN @Results RS
ON RS.a1 = TableB.b2
WHERE RS.a1 IS NULL
GROUP BY TableB.b2
)

--All that's left is to return the results
SELECT TableA.*, RS.Rank AS Total_Rank
FROM TableA INNER JOIN @Results RS
ON TableA.a1 = RS.a1
ORDER BY RS.Rank DESC

This isn't as elegant as using one query, but it should be easy to follow and allows you to decide whether or not to include records in the 3rd query.

这不像使用一个查询那么优雅,但它应该很容易遵循,并允许您决定是否在第三个查询中包含记录。

#1