title: full text search, filter results for best match
body: <p>I have 3 tables that link up. Restaurants, Cuisines and Cuisine Type. 
  A Restaurant can sell many cuisines of food (that's bad wording but you get idea?)</p>
  
  <p>So I have Full text setup on Restaurant:Name, CityTown, Postcode and on CuisineType:Name</p>
  
  <p>I have one searchbox on my home page and as the user types results are filtered to best match.</p>
  
  <p>Here are a few examples:
  SearchText= "Royal D Ed"
  Now there is a row in Restaurant for a place named Royal Dynasty and the town is Edinburgh.
  But my top result back is for somewhere wher town starts with D aswell as name. This is not the best match.</p>
  
  <p>I will show you my stored proc, prototype so deriveed names are a bit hackish.</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[RestaurantsFullText]
      @searchText nvarchar(255) 
  AS
  SELECT 
      b.*, 
      COALESCE(akt2.[Rank],0) / 30 + 
      COALESCE(akt1.[Rank],0) / 30 + 
      COALESCE(akt.[Rank],0) / 30 + 
      COALESCE(bkt.[Rank],0)  as rankCount  
  FROM 
      Restaurants b 
  left JOIN Cuisines c on b.Id = c.RestaurantId 
  left join CuisineType a 
  ON c.CuisineId = a.id 
  
  left JOIN 
  containstable(Restaurants, Name, @searchText) bkt 
  ON b.id = bkt.[Key] 
  
  left JOIN containstable(CuisineType, Name, @searchText) akt 
  ON a.id = akt.[Key]
  
  left JOIN containstable(Restaurants, Postcode, @searchText) akt1 
  ON b.id = akt1.[Key]
  
  left JOIN containstable(Restaurants, citytown, @searchText) akt2 
  ON b.id = akt2.[Key]
  
  where 
      COALESCE(akt2.[Rank],0) / 30 + 
      COALESCE(akt1.[Rank],0) /30 + 
      COALESCE(akt.[Rank],0) / 30 + 
      COALESCE(bkt.[Rank],0)  &gt; 5
  ORDER BY 
      COALESCE(akt2.[Rank],0) / 30 + 
      COALESCE(akt1.[Rank],0) / 30 + 
      COALESCE(akt.[Rank],0) / 30 + 
      COALESCE(bkt.[Rank],0)   asc
  </code></pre>
  
  <p>I think the problem lies in the joins and the way rank is calculated.</p>
  
  <p>I want it so that if I pass in "Royal Dynasty Edinburgh d" then Royal Dynasty is still the best match.</p>
  
  <p>Since it is a filter then matches for towns in other cities starting with d should not be returned.</p>
  
  <p>I would be extremely greatrful for help on this.</p>