Full Text Search 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? I was thinking of creating a
view to combine all the tables, but that doesn't work either, since the view
wouldn't have a unique column (which FTS needs)
Iron,
If I understand your question this might help. I've done this before with
company names, addresses and phone numbers and other company information
contained in multiple tables.
1. Create another table with a column that concatinates all the columns you
want to index. It would require a composite primary key of actorid, sceneid,
and movieid. I am assuming that this relationship exists in your database.
2. Configure your full text catalog to work with the new table and column.
3. Populate the new table.
4. Apply your full text queries against the new table and join to the other
tables to return the recordsets.
I duplicates data, but it works great.
-- Bill
"IronYuppie" <IronYuppie@.discussions.microsoft.com> wrote in message
news:E5C1BB12-8C96-4EE2-81CA-9157E67C388E@.microsoft.com...
> 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? I was thinking of creating a
> view to combine all the tables, but that doesn't work either, since the
> view
> wouldn't have a unique column (which FTS needs)

Full Text Search 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... ho
w
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? I was thinking of creating a
view to combine all the tables, but that doesn't work either, since the view
wouldn't have a unique column (which FTS needs)Iron,
If I understand your question this might help. I've done this before with
company names, addresses and phone numbers and other company information
contained in multiple tables.
1. Create another table with a column that concatinates all the columns you
want to index. It would require a composite primary key of actorid, sceneid,
and movieid. I am assuming that this relationship exists in your database.
2. Configure your full text catalog to work with the new table and column.
3. Populate the new table.
4. Apply your full text queries against the new table and join to the other
tables to return the recordsets.
I duplicates data, but it works great.
-- Bill
"IronYuppie" <IronYuppie@.discussions.microsoft.com> wrote in message
news:E5C1BB12-8C96-4EE2-81CA-9157E67C388E@.microsoft.com...
> 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? I was thinking of creating a
> view to combine all the tables, but that doesn't work either, since the
> view
> wouldn't have a unique column (which FTS needs)

Full text matching and quotes

I have data in my full text column such as "men's shoes". When a user does a
search such as "mens shoes" it does not return a match, because the user data
does not contain the quote. If they search on "men's shoes" then it works
properly.
Anybody run into this and have ideas about how to deal with this situation?
Granted it's basically a user error, but I wanted to see if anyone has ideas?
Thanks
Can you do a FreeText query?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:49595D7D-4338-4C5A-BF5A-73C34740FE64@.microsoft.com...
>I have data in my full text column such as "men's shoes". When a user does
>a
> search such as "mens shoes" it does not return a match, because the user
> data
> does not contain the quote. If they search on "men's shoes" then it works
> properly.
> Anybody run into this and have ideas about how to deal with this
> situation?
> Granted it's basically a user error, but I wanted to see if anyone has
> ideas?
> Thanks
|||I give the user the option to do an 'AND' or an 'OR' with search terms. My
understanding of FreeText is that it is always an 'AND' and I don't have the
option for 'OR'. Is that correct?
"Hilary Cotter" wrote:

> Can you do a FreeText query?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
> news:49595D7D-4338-4C5A-BF5A-73C34740FE64@.microsoft.com...
>
>
|||FreeText is an OR. Contains will be a strict phrase based search unless you
make it Boolean.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:8FFCB088-055B-4DFF-99C6-B9E123936523@.microsoft.com...[vbcol=seagreen]
>I give the user the option to do an 'AND' or an 'OR' with search terms. My
> understanding of FreeText is that it is always an 'AND' and I don't have
> the
> option for 'OR'. Is that correct?
> "Hilary Cotter" wrote: