SQL Server FullText Search with Weighted Columns from Previous One Column
<p>In the database on which I am attempting to create a FullText Search I need to construct a table with its column names coming from one column in a previous table. In my current implementation attempt the FullText indexing is completed on the first table <code>Data</code> and the search for the phrase is done there, then the second table with the search results is made.</p>

<p>The schema for the database is</p>

<pre><code>**Players**

  Id
  PlayerName
  Blacklisted
  ...

**Details**
  Id
  Name -&gt; FirstName, LastName, Team, Substitute, ...
  ...

**Data**
  Id
  DetailId
  PlayerId
  Content
</code></pre>

<p><code>DetailId</code> in the table <code>Data</code> relates to <code>Id</code> in <code>Details</code>, and <code>PlayerId</code> relates to <code>Id</code> in <code>Players</code>. If there are 1k rows in <code>Players</code> and 20 rows in <code>Details</code>, then there are 20k rows in <code>Data</code>.</p>

<pre><code>WITH RankedPlayers AS
(
    SELECT PlayerID, SUM(KT.[RANK]) AS Rnk
    FROM Data c
    INNER JOIN FREETEXTTABLE(dbo.Data, Content, '"Some phrase like team name and player name"')
    AS KT ON c. DataID = KT.[KEY]
    GROUP BY c.PlayerID 
)
…
</code></pre>

<p>Then a table is made by selecting the rows in one column. Similar to a pivot.</p>

<pre><code>…
SELECT rc.Rnk, 
    c.PlayerID, 
    PlayerName, 
    TeamID,
    …
    (SELECT Content FROM dbo.Data data WHERE DetailID = 1 AND data.PlayerID = c.PlayerID) AS [TeamName],
    …
FROM dbo.Players c
JOIN RankedPlayers rc ON c. PlayerID = rc. PlayerID
ORDER BY rc.Rnk DESC
</code></pre>

<p>I can return a ranked table with this implementation, the aim however is to be able to produce results from weighted columns, so say the column <code>Playername</code> contributes to the rank more than say <code>TeamName</code>.</p>

<p>I have tried making a schema bound view with a pivot, but then I cannot index it because of the pivot. I have tried making a view of that view, but it seems the metadata is inherited, plus that feels like a clunky method.</p>

<p>I then tried to do it as a straight query using sub queries in the select statement, but cannot due to indexing not liking sub queries.</p>

<p>I then tried to join multiple times, again the index on the view doesn't like self-referencing joins.</p>

<p>How to do this?</p>

<p>I have come across this article <a href="http://developmentnow.com/2006/08/07/weighted-columns-in-sql-server-2005-full-text-search/" rel="nofollow">http://developmentnow.com/2006/08/07/weighted-columns-in-sql-server-2005-full-text-search/</a> , and other articles here on weighted columns, however nothing as far as I can find addresses weighting columns when the columns were initially row data.</p>