Using Full-Text Search in SQL Server 2008 across multiple tables, columns

Question

I need to search across multiple columns from two tables in my database using Full-Text Search. The two tables in question have the relevant columns full-text indexed.

The reason I'm opting for Full-text search: 1. To be able to search accented words easily (cafè) 2. To be able to rank according to word proximity, etc. 3. "Did you mean XXX?" functionality

Here is a dummy table structure, to illustrate the challenge:

Table Book
BookID
Name (Full-text indexed)
Notes (Full-text indexed)

Table Shelf
ShelfID
BookID

Table ShelfAuthor
AuthorID
ShelfID

Table Author
AuthorID
Name (Full-text indexed)

I need to search across Book Name, Book Notes and Author Name.

I know of two ways to accomplish this:

1. Using a Full-text Indexed View: This would have been my preferred method, but I can't do this because for a view to be full-text indexed, it needs to be schemabound, not have any outer joins, have a unique index. The view I will need to get my data does not satisfy these constraints (it contains many other joined tables I need to get data from).

2. Using joins in a stored procedure: The problem with this approach is that I need to have the results sorted by rank. If I am making multiple joins across the tables, SQL Server won't search across multiple fields by default. I can combine two individual CONTAINS queries on the two linked tables, but I don't know of a way to extract the combined rank from the two search queries. For example, if I search for 'Arthur', the results of both the Book query and the Author query should be taken into account and weighted accordingly.

Answer 1

I would use a stored procedure. The full text method or whatever returns a rank which you can sort by. I am not sure how they will be weighted against eachother, but I'm sure you could tinker for awhile and figure it out. For example:

Select SearchResults.key, SearchResults.rank From FREETEXTTABLE(myColumn, *, @searchString) as SearchResults Order By SearchResults.rank Desc

Answer 2

Using FREETEXTTABLE, you just need to design some algorithm to calculate the merged rank on each joined table result. The example below skews the result towards hits from the book table.

SELECT b.Name, a.Name, bkt.[Rank] + akt.[Rank]/2 AS [Rank]
FROM Book b
INNER JOIN Author a ON b.AuthorID = a.AuthorID
INNER JOIN FREETEXTTABLE(Book, Name, @criteria) bkt ON b.ContentID = bkt.[Key] 
LEFT JOIN FREETEXTTABLE(Author, Name, @criteria) akt ON a.AuthorID = akt.[Key]
ORDER BY [Rank] DESC

Note that I simplified your schema for this example.

Answer 3

I don't think the accepted answer will solve the problem. If you try to find all the books from a certain author and, therefore, use the author's name (or part of it) as the search criteria, the only books returned by the query will be those which have the search criteria in its own name.

The only way I see around this problem is to replicate the Author's columns that you wish to search by in the Book table and index those columns (or column since it would probably be smart to store the author's relevant information in an XML column in the Book table).