FullText Search using multiple tables in SQL
- by Caesar
Hi there. I have 3 tables,
tblBook(BookID, ISBN, Title, Summary)
tblAuthor(AuthorID, FullName)
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?
