How do I use full text search across multiple tables, SQL Server 2005

Question

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

tableA has 4 columns (a1, a2, a3, a4) of which 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.

Answer 1

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

Answer 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.

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

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.