title: Issue adding SQL FullText Search to an existing SELECT statement
body: <p>I'm trying to do a full text search but am having issues implementing it.  I have an Object which can have multiple "Titles".  I have a stored procedure that takes a language and country and returns a normalized result with the best "Title" based on the localization.
  What I want to be able to do is perform a full text search on the "Title" table that will search all rows but then only return the normalized, default title.</p>
  
  <p>To get the normalized result I have something like</p>
  
  <pre><code>NORMALIZING STATEMENT
  
  SELECT MainObject.*, Title.Name, Description.Name
  FROM MainObject
  OUTER APPLY
  (
      SELECT TOP 1 Title.Name
      FROM Title
      WHERE Title.MainObject_ID = MainObject.ID AND Title.Language = @language
      ORDER BY Title.[Default] DESC
  )
  OUTER APPLY
  (
      SELECT TOP 1 Description.Name
      FROM Description
      WHERE Description.MainObject_ID = MainObject.ID AND Description.Language = @language
      ORDER BY Description.[Default] DESC
  )
  </code></pre>
  
  <p>Say an object has 3 titles, Alpha, Bravo and Charlie with Alpha being the default Title.
  I want to be able to do a full text search for Bravo.  The object that Bravo is linked to is found but is normalized with the above statement.  I.E search for "Bravo" and "Alpha" is returned as Title.Name.</p>
  
  <p>The only way I can think of doing this is to first perform the Search such as</p>
  
  <pre><code>FULL-TEXT STATEMENT
  
  SELECT Title.MainObject_ID, MAX(KEY_TBL.RANK)
  FROM Title
  INNER JOIN
  FREETEXTTABLE(Title,Name,'Some Search') AS KEY_TBL
  ON Title.ID = KEY_TBL.[KEY]
  WHERE KEY_TBL.RANK &gt;= 50
  GROUP BY Title.MainObjectID, KEY_TBL.RANK
  ORDER BY KEY_TBL.RANK DESC
  </code></pre>
  
  <p>This returns a list of MainObjects.  I can then run these through the top statement.  Is there a way to combine the two in a more elegant manor to thus get better performance?</p>
  
  <p>UPDATE:
  I can perform a WHERE IN on the full text statement but I'm not sure how to get the RANK back out to the main statement so I can have final output ordered by descending RANK.</p>
  
  <pre><code>NORMALIZING STATEMENT
  WHERE MainObject.ID IN
  (
      FULL-TEXT STATEMENT
  )
  </code></pre>