More on Full Text Searching across multiple tables

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?

More grouping Woes!

I have a result set of consisting of names and addresses. each person at a
particular address (1-n people) gets an identical groupid to group them in a
"Family".
My report lists each column, groupid, name, address, with one row for each
person. I would like for it to group on the groupid, then list each person in
a subgroup sorted by name.
When I set a "group" row on my report and group on the groupid, all i get is
the first row of the family, and the rest of the data is dropped. how do i
get the group to show all the data? I am using RS2005 with VS2005.
thank you in advance for your help!!I ran into a similar problem a little while ago. I had numerical data based
on the # of hours worked and then a description of what was done in those
hours. I grouped on the person and date and then summed the hours they
worked for that span of time so my output looked something like:
John Doe
11/12/2006
Regular OT Description
On-Site 8 0 Worked on
server.
Remote 0 2
However the description for the remote would not print out, even though the
output was =fields!onsitenotes.value & fields!remotenotes.value, because they
were in the same group
What I wound up doing was setting a different group underneath the name/date
for each row of data(on-site, remote) I'm not sure if you will be able to do
exactly that but I basically had to assign some flags based on the work-type
(on-site, remote, etc) in the query and then group on those flags.
So it wound up being something like:
Group1(
John Doe
11/12/2006
Regular OT
Description
Group2 ( On-Site 8 0 Worked
on server.)
Group3 ( Remote 0 2 Helped on
the phone.)
)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200612/1