title: Using Full-Text Search in SQL Server 2008 across multiple tables, columns
body: <p>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.</p>
  
  <p>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</p>
  
  <p>Here is a <strong>dummy</strong> table structure, to illustrate the challenge:</p>
  
  <pre>
  <strong>Table Book</strong>
  BookID
  Name (Full-text indexed)
  Notes (Full-text indexed)
  
  <strong>Table Shelf</strong>
  ShelfID
  BookID
  
  <strong>Table ShelfAuthor</strong>
  AuthorID
  ShelfID
  
  <strong>Table Author</strong>
  AuthorID
  Name (Full-text indexed)
  </pre>
  
  <p><strong>I need to search across Book Name, Book Notes and Author Name.</strong> </p>
  
  <p>I know of two ways to accomplish this:</p>
  
  <ol>
  <li><p><strong>Using a Full-text Indexed View:</strong> 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).</p></li>
  <li><p><strong>Using joins in a stored procedure</strong>: 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 <strong>combined</strong> 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. </p></li>
  </ol>