Is this possible with Full Text Indexing?

(SQL Server 2000, SP4)
Hello all!
I am wrestling with a problem that I hope someone can help me with. I have
a series of related tables (fairly normalized), each of which has a textual
column whose data will, for the most part, be unique from the other tables.
We have created a Full Text Catalog over these tables that will index these
text columns.
We are trying to write an interface to search over the entire "suite" of
tables, using an expression like "A and B" or "A or B". The problem arises
when we try and craft the appropriate query using the Full Text CONTAINS
predicate.
For example, lets say that we have Shape, Color, and Size tables. If
someone tries to search for "Circle or Red", I can pretty easily find the
appropriate records from each of the tables. However, if I try and search
for "Circle and Red", I'm having a more difficult time expressing the query
to restrict the results to only those records that match.
It's almost like I need a Full Text Catalog over the *entire* set of tables.
Working with the individual tables is giving me fits.
Any help/advice would be *much* appreciated. Thanks!
John Peterson
In SQL 2005 you can create an indexed view over all tables and the full text
index the view. In SQL 2000 I think you best bet would be to create a table
which would contain the rows from all tables, full-text index it, and then
search it.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u26x%23Sy8GHA.4084@.TK2MSFTNGP05.phx.gbl...
> (SQL Server 2000, SP4)
> Hello all!
> I am wrestling with a problem that I hope someone can help me with. I
> have a series of related tables (fairly normalized), each of which has a
> textual column whose data will, for the most part, be unique from the
> other tables. We have created a Full Text Catalog over these tables that
> will index these text columns.
> We are trying to write an interface to search over the entire "suite" of
> tables, using an expression like "A and B" or "A or B". The problem
> arises when we try and craft the appropriate query using the Full Text
> CONTAINS predicate.
> For example, lets say that we have Shape, Color, and Size tables. If
> someone tries to search for "Circle or Red", I can pretty easily find the
> appropriate records from each of the tables. However, if I try and search
> for "Circle and Red", I'm having a more difficult time expressing the
> query to restrict the results to only those records that match.
> It's almost like I need a Full Text Catalog over the *entire* set of
> tables. Working with the individual tables is giving me fits.
> Any help/advice would be *much* appreciated. Thanks!
> John Peterson
>
|||Dear Hilary,
Thank you for the information! I didn't realize that SQL 2005 had that
capability (to full-text an Indexed VIEW) -- that's really neat! I'll have
to look into that a bit more...
Yeah, if we stick with SQL 2000, our thought was to build a side table as
you suggest.
Thanks again!
John Peterson
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uhkSJm68GHA.3264@.TK2MSFTNGP04.phx.gbl...
> In SQL 2005 you can create an indexed view over all tables and the full
> text index the view. In SQL 2000 I think you best bet would be to create a
> table which would contain the rows from all tables, full-text index it,
> and then search it.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u26x%23Sy8GHA.4084@.TK2MSFTNGP05.phx.gbl...
>
sql