Full Text Search and Virtual Server

Does anyone know if there is a way to turn off (and hide
the search form) for a single Virtual Server? I know I
can turn it off across all servers, but I need to turn it
off for just one. I have a site that consists of a lot of
RSS feeds, which are of course not searchable. My users
are getting confused! THANKS.Stephen,
I'm not exactly sure what you're trying to do here. Are you referring to a
cluster's failover virtual server?
Could you provide more details?
Note, you can also post FTS related questions to the newsgroup:
microsoft.public.sqlserver.fulltext.
Regards,
John
"Stephen Haiman" <shaiman@.ctt-inc.com> wrote in message
news:19c001c3a8aa$864e7020$a601280a@.phx.gbl...
> Does anyone know if there is a way to turn off (and hide
> the search form) for a single Virtual Server? I know I
> can turn it off across all servers, but I need to turn it
> off for just one. I have a site that consists of a lot of
> RSS feeds, which are of course not searchable. My users
> are getting confused! THANKS.sql

Full Text Search across multiple tables (Parent-Child)

Is is possible to search across multiple tables (Parent-Child)?
contact(contact_id, name)
role(role_id, description)
contact_role (contact_id, role_id)
How can I find all contact where the search string is in the contact
name and or roles description?
I tried to search on contact then on role and join the result but it
doesn't work when I search for multiple terms ie. (smith and director)
since all terms may not appears in the parent and child table.
Thanks.
This is the quick and simplest way. You can rewrite this query using
outer-joins. The code is not tested. So expect some syntax errors.
select * from contact where contactid in
(
select contactid from contact_role where contactid in (select key from
containstable(contact,name,'search string') or roleid in (select key
from containstable(role,description,'search string')
)
martalex@.hotmail.com (Alex) wrote in message news:<71a623cb.0405031741.37e0cf5b@.posting.google. com>...
> Is is possible to search across multiple tables (Parent-Child)?
> contact(contact_id, name)
> role(role_id, description)
> contact_role (contact_id, role_id)
> How can I find all contact where the search string is in the contact
> name and or roles description?
> I tried to search on contact then on role and join the result but it
> doesn't work when I search for multiple terms ie. (smith and director)
> since all terms may not appears in the parent and child table.
> Thanks.
sql

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)

FTS newbie - Searching accross multiple tables using CONTAINSTABLE

Hi all
I have a database with a number of tables with related information. I would
like to do a search across all these tables using AND, OR etc, which means
using CONTAINSTABLE, right?
Using something like:
searchStr = "dog AND cat"
SELECT a FROM parenttable
WHERE parentid IN
(SELECT [KEY] FROM CONTAINSTABLE(childtable1, *, searchStr))
OR parentid IN
(SELECT [KEY] FROM CONTAINSTABLE(childtable2, *, searchStr))
I get some results... The problem is, of course, that this means that both
dog and cat have to be in each child table, but I want to get all parent
table rows where dog AND cat appear ANYWHERE in the child tables (eg cat in
one and dog in the other, or both in one child table and none in the other,
and so on). Is this possible? Any suggestions?
This would be used on a search page on an ASP site. The real tables are more
like grandparent - parent - child, child, child.
Thanks a lot in advance!
Marcus
does the key column correspond to the pk of the parent table?
If so, this should work.
|||Thanks for the reply!
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:3BEBA99D-395A-4186-AAC6-6F22F7FE9E28@.microsoft.com...
> does the key column correspond to the pk of the parent table?
> If so, this should work.
Yes, the key column corresponds to the pk of the parent, but it doesn't
work. I am testing on some records where all the rows of child1 contain
"cat" and all the rows of child2 contain "dog". If I set the search string
to "cat" I get all the parent rows, like I should. Likewise, I get all rows
if I set the search string to "dog", of course. However, if I set the search
string to "cat AND dog" I get no rows.
This is not particularly surprising to me, since each "child query" executed
independently returns zero keys for "cat AND dog". Am I missing something
here? What can I do?
Here's the query again, for readability:
SELECT a FROM parenttable
WHERE parentid IN
(SELECT [KEY] FROM CONTAINSTABLE(childtable1, *, searchStr))
OR parentid IN
(SELECT [KEY] FROM CONTAINSTABLE(childtable2, *, searchStr))
Thank you!
Regards,
Marcus
|||No replies... So I take it this is not possible? Any suggestions on other
solutions?
Thanks,
Marcus
"Marcus" <lumbus@.ludd.luth.se> wrote in message
news:uXlNUDROEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply!
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:3BEBA99D-395A-4186-AAC6-6F22F7FE9E28@.microsoft.com...
> Yes, the key column corresponds to the pk of the parent, but it doesn't
> work. I am testing on some records where all the rows of child1 contain
> "cat" and all the rows of child2 contain "dog". If I set the search string
> to "cat" I get all the parent rows, like I should. Likewise, I get all
rows
> if I set the search string to "dog", of course. However, if I set the
search
> string to "cat AND dog" I get no rows.
> This is not particularly surprising to me, since each "child query"
executed
> independently returns zero keys for "cat AND dog". Am I missing something
> here? What can I do?
> Here's the query again, for readability:
> SELECT a FROM parenttable
> WHERE parentid IN
> (SELECT [KEY] FROM CONTAINSTABLE(childtable1, *, searchStr))
> OR parentid IN
> (SELECT [KEY] FROM CONTAINSTABLE(childtable2, *, searchStr))
> Thank you!
> Regards,
> Marcus
>
|||Marcus,
I had assumed that from your previous "Thanks for the reply" reply that the
[Primary] key (PK) column corresponds to the PK in the parent table and
therefore is solution was viable for you. Is that not correct? If so, then
could you provide more details on your table structures and the real SQL FTS
query?
Thanks,
John
"Marcus" <lumbus@.ludd.luth.se> wrote in message
news:uBT$PwdPEHA.4036@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> No replies... So I take it this is not possible? Any suggestions on other
> solutions?
> Thanks,
> Marcus
> "Marcus" <lumbus@.ludd.luth.se> wrote in message
> news:uXlNUDROEHA.2876@.TK2MSFTNGP09.phx.gbl...
string[vbcol=seagreen]
> rows
> search
> executed
something
>
|||John,
Thanks for the reply. Either I'm missing something here or there is some
confusion about what I'm looking for. I'll try to explain this very clearly:
My real tables are pretty complicated and I think they will only confuse. I
have made a test database that looks like this:
TABLE [Parent]:
[ParentID] [int] IDENTITY (1, 1) NOT NULL
[Data] [varchar] (100)
TABLE [Child1]:
[ParentID] [int] NOT NULL ,
[Data] [varchar] (100)
TABLE [Child2]:
[ParentID] [int] NOT NULL ,
[Data] [varchar] (100)
I then insert the following rows:
TABLE [Parent]:
ParentID=1, Data='dummy'
TABLE [Child1]:
ParentID=1, Data='the dog says woof'
TABLE [Child2]:
ParentID=1, Data='the cat says miau'
Now, remember: What I want to do is to search for "dog AND cat" in this
structure, returning the IDs for the parent rows where the words "cat and
"dog" appear anywhere in the tables, for example "cat" in Child1 and "dog"
in Child2.
This query...
SELECT ParentID FROM Parent
WHERE ParentID IN
(SELECT [KEY] FROM CONTAINSTABLE(Child1, *, 'cat AND dog'))
OR ParentID IN
(SELECT [KEY] FROM CONTAINSTABLE(Child2, *, 'cat AND dog'))
...returns nothing. This is the problem I'm asking about.
Just to show that the tables are healthy, this query...
SELECT ParentID FROM Parent
WHERE ParentID IN
(SELECT [KEY] FROM CONTAINSTABLE(Child1, *, 'cat OR dog'))
OR ParentID IN
(SELECT [KEY] FROM CONTAINSTABLE(Child2, *, 'cat OR dog'))
...returns ParentID=1. And these queries...
SELECT [KEY] FROM CONTAINSTABLE(Child1, *, 'cat OR dog')
SELECT [KEY] FROM CONTAINSTABLE(Child2, *, 'cat OR dog')
...return KEY=1.
So the question is whether there is a solution to this or whether I should
give up on using FTS for my search functionality.
Thank you!
Regards,
Marcus
"John Kane" <jt-kane@.comcast.net> wrote in message
news:Op2OC3gPEHA.3524@.TK2MSFTNGP09.phx.gbl...
> Marcus,
> I had assumed that from your previous "Thanks for the reply" reply that
the
> [Primary] key (PK) column corresponds to the PK in the parent table and
> therefore is solution was viable for you. Is that not correct? If so, then
> could you provide more details on your table structures and the real SQL
FTS[vbcol=seagreen]
> query?
> Thanks,
> John
>
> "Marcus" <lumbus@.ludd.luth.se> wrote in message
> news:uBT$PwdPEHA.4036@.TK2MSFTNGP12.phx.gbl...
other[vbcol=seagreen]
doesn't[vbcol=seagreen]
contain
> string
> something
>