FullText Search using multiple tables in SQL
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开发者_C百科 me with this?
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.
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/
精彩评论