Limiting search space when using CONTAINSTABLE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Limiting search space when using CONTAINSTABLE

Author

Topic

einsib
Starting Member

4 Posts

Posted - 2008-01-02 : 08:13:29

Does anyone know a way to limit the search space (the set of rows being searched in) when using the CONTAINSTABLE statement?

Let's say I have a table called BlogPosts containing blog posts for number of different blog sites. This table is full-text indexed.
The table could look something like this:
----------------------------------
PostID | Title | Body | BlogSiteID
----------------------------------
where the BlogSiteID filed is an integer to indicate which blog site the blog entry belongs to.

I want to search the blog entries ONLY from one specific blog for some word or phrase.
How can I limit the rows CONTAINSTABLE has to scan through, to only one blog site?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-02 : 09:47:56

Look for the weightage and NEAR option of CONTAINSTABLE:-

[url]http://msdn2.microsoft.com/en-us/library/ms189760.aspx[/url]

einsib
Starting Member

4 Posts

Posted - 2008-01-02 : 09:56:57

I'm sorry, but I can't see how the weightage nor the NEAR option could help me in this case.

If I'm correct, the weightage operator only defines how a specific word in the search should be weighted and the near operator specifies that the words being search for should stand near to each other within the text. How can this help me filtering the rows that the CONTAINSTABLE statement has to search through?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-02 : 10:07:49

Sorry i overlook it a bit. I guess for restricting CONTAINSTABLE to scan only one BlogSiteID records i think you should use a derived table to take only the interested rows and then join with CONTAINSTABLE

i.e something like

SELECT fields
FROM (SELECT fields 
      FROM Table
      Where BlogSiteID=value) t
INNER JOIN CONTAINSTABLE(Table,Body,condition)k
ON t.PostID=k.[KEY]

einsib
Starting Member

4 Posts

Posted - 2008-01-02 : 11:07:43

Thank you for the reply.

Using a derived table in the FROM statement does not help since the results of the CONTAINSTABLE call are joined with the results of the derived table. This means that the results from the CONTAINSTABLE call are all the matching blog entries not matter which BlogSiteID they belong to.

Unfortunently I cannot use a derived table in the CONTAINSTABLE statement like this:
SELECT fields
FROM (SELECT fields
FROM Table
Where BlogSiteID=value) t
INNER JOIN CONTAINSTABLE((SELECT fields
FROM Table
Where BlogSiteID=value),Body,condition) k
ON t.PostID=k.[KEY]

So I ask again, is there any way to limit the search space?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-02 : 11:10:48

Try using a temp table then

INSERT @Temp
SELECT fields
FROM Table
Where BlogSiteID=value

and use CONTAINSTABLE(@Temp,Body,condition)

einsib
Starting Member

4 Posts

Posted - 2008-01-02 : 11:34:13

The MS SQL 2000 does not seem to support the CONTAINSTABLE on a temporary table or a table variable.
Using a table variable as the first argument in the CONTAINSTABLE method, the SQL server returns:
Incorrect syntax near '@Temp'
Using a temp table (#table) produces this error:
Cannot use a CONTAINS or FREETEXT predicate on table '#Temp' because it is not full-text indexed.

This is probably because the SQL server does not have a full-text index for the newly created temp-table/table-variable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources