SQL Server Full-Text Search - Many-to-Many Relationship

Question

I am working on a project with SQL Server 2008 where I believe that full-text search is the best way. I read on it as much as I could, and I understand that he wanted to configure it for one table. However, I'm not quite sure how to configure it using my script - imagine the following table structure:

Book

- Id - Title - Description 

Bookauthor

 - BookId - AuthorId 

Author

 - Id - Name 

As you can see, the database contains a table with books, and in each book there is not one, one or many authors. Each author can also be part of not one, one or many books, i.e. The Book and Author tables are many-to-many tables processed by the BookAuthor binding BookAuthor .

What I want to do at this point is a search tool to find matching books based on the search string that the user provides. Therefore, if the user enters Brown , I would like to find all books that contain the word Brown in one of the following columns:

 Book.Title Book.Description Author.Name 

In essence, I need a resulting set of books, including a book called Brown Bear and books written by Dan Brown . If there are any suggestions on how I should do this, I would really appreciate your input!

(as a side note, as soon as I work with this filtering, the query result should also be sortable and accessible for the page, processed via @SortOrder , @PageIndex and @PageSize in the stored procedure - but I think this may be a separate issue later! )

Answer 1

The CONTAINS predicate can take a list of columns to search as its first argument; however, these columns must be from the same table. You have several options to get around this limitation. One option is that you can perform two separate searches, one for each table, and then JOIN the results together.

 select Id, Title from Book where contains([Description], 'brown') union select b.Id, b.Title from Book b inner join BookAuthor ba on b.Id = ba.BookId inner join Author a on a.Id=ba.AuthorId where contains([Name], 'brown') 

Another option is to use the fact that FTS indices can be created in indexed views . To do this, create an indexed view containing both the Title field from the Book table and the Name field in the Author table, and then create the FTS index for both of these columns in the view. Then you can write queries in this view as follows:

 select BookId, Title from vw_BooksAndAuthors where contains(([Description], [Name]), 'brown')