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?