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 requried to full-text search over multiple tables.