Imagine you're building a search for a movie database which you wanted to index actors, quotes from scenes and movie names into a single search.... how do you accomplish this using full text search? The closest I can think of is... (for a sample search for "Walken"
select RANK, actorId as Id from FREETEXTTABLE( actors, *, 'ISABOUT (+ WALKEN + WEIGHT(1.0))') a JOIN actors b on a.[key] = b.actorid
UNION ALL
select RANK, sceneId as Id from FREETEXTTABLE( scenes, *, 'ISABOUT (+ WALKEN + WEIGHT(1.0))') a JOIN scenes b on a.[key] = b.sceneId
UNION ALL
select RANK, movieId as Id from FREETEXTTABLE( movie, *, 'ISABOUT (+ WALKEN + WEIGHT(1.0))') a JOIN movie b on a.[key] = b.movieId
But it doesn't rank correctly. Suggestions?
How do you want the ranking to work?
What do you want to return? have you tried
select RANK, actorId as Id from CONTAINSTABLE( actors, *, 'WALKEN') a JOIN actors b on a.[key] = b.actorid
UNION ALL
select RANK, sceneId as Id from CONTAINSTABLE( scenes, *, 'WALKEN') a JOIN scenes b on a.[key] = b.sceneId
UNION ALL
select RANK, movieId as Id from CONTAINSTABLE( movie, *, 'WALKEN') a JOIN movie b on a.[key] = b.movieId
The other thing to note is that RANK is not a fixed number it is relative to a number of factors, largely it is related to the batch of data a record was indexed with. So if you have records being indexed in differing batchs you can end up with odd ranking.
Bottom linke is a rank from tableA can't be used to compare with a rank from tableB
|||Ideally, i'd like to return the item(s), in order that match the query string. This means if the scene is a closer match than the movie, the scene is returned first. Each one should have the id returned which i can then construct a result from.|||Unfortunately you can't use the rank from one index to compare with a rank from another index.
Have a lookin BOL under the heading "Understanding Ranking "
|||Right, what i'm looking for is a solution that will work across multiple tables... any suggestions?