How do I use full text search across multiple tables, SQL Server 2005
full-text-search, multiple-tables, sql-server-2005
Solution
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
Problem
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.