FullText Search using multiple tables in SQL

Question

Hi there. I have 3 tables,

1. tblBook(BookID, ISBN, Title, Summary)
2. tblAuthor(AuthorID, FullName)
3. tblBookAuthor(BookAuthorID, BookID, AuthorID)

tblBookAuthor allows for a single book to have multiple authors and an author may have written any number of books.

I am using full text search to search for ranking base on a word:

SET @Word = 'FORMSOF(INFLECTIONAL, "' + @Word + '")'

SELECT 
  COALESCE(ISBNResults.[KEY], TitleResults.[KEY], SummaryResults.[KEY]) AS [KEY],
  ISNULL(ISBNResults.Rank, 0) * 3 +
  ISNULL(TitleResults.Rank, 0) * 2 +
  ISNULL(SummaryResults.Rank, 0) AS Rank
FROM
  CONTAINSTABLE(tblBook, ISBN, @Word, LANGUAGE 'English') AS ISBNResults
  FULL OUTER JOIN 
    CONTAINSTABLE(tblBook, Title, @Word, LANGUAGE 'English') AS TitleResults 
    ON ISBNResults.[KEY] = TitleResults.[KEY]
  FULL OUTER JOIN
    CONTAINSTABLE(tblBook, Summary, @Word, LANGUAGE 'English') AS SummaryResults 
    ON ISBNResults.[KEY] = SummaryResults.[KEY]

The above code works fine for just searching tblBook table. But now I would like to search also the table tblAuthor based on key word searched.

Can you help me with this?

Answer 1

You could run another SELECT/CONTAINSTABLE query on tblAuthor, Union the results together and wrap with another query which sums the Rank over the Key column to remove any duplicates and push results with both tblBook and tblAuthor matches up higher.

I think this will achieve what you are trying todo.

Answer 2

You could create a view that combines the tables, and then create a full text index on that. See this blog post: http://versia.com/2008/02/06/sql-server-full-text-searching-across-multiple-tables/