Full Text Search on Two columns

How do I use FREETEXTTABLE on two columns in a table but rank by only one
column.
For example I have two columns News_Summary,News_Content
In this case the user can specify during search if he wants to search
News_Summary or the News_Content or Both.There also other columns in the
tables.
In any case I want to rank only by News_Content.
What is the easiest way to accomplish this.
This is the Query I am using right now,How do I add one more column to this.
I dont want to use mutlple stored procedures.
SELECT *
FROM news AS FT_TBL INNER JOIN
FREETEXTTABLE(news,news_summary,'FORMSOF(INFLECTIO NAL, "Report")') AS KEY_TBL
ON FT_TBL.news_id= KEY_TBL.[KEY]
WHERE(
expire_dtm>getdate()
)
"Bruce" wrote:

> How do I use FREETEXTTABLE on two columns in a table but rank by only one
> column.
> For example I have two columns News_Summary,News_Content
> In this case the user can specify during search if he wants to search
> News_Summary or the News_Content or Both.There also other columns in the
> tables.
> In any case I want to rank only by News_Content.
> What is the easiest way to accomplish this.
>
|||Bruce,
You can use the following examples of multiple FREETEXTTABLE for multiple
columns in the same table (Employees). Note the "AND" condition between the
Primary Keys, you can also change to an "OR" condition.
-- multiple columns from one FT enable table using FREETEXTTABLE in the
Northwind database
use Northwind
go
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
freetexttable(Employees, Notes, 'BA') as A,
freetexttable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID AND
B.[KEY] = e.EmployeeID
use pubs
go
SELECT pub_id, pr_info FROM pub_info
WHERE FREETEXT(pr_info, ' "publish*" ') AND FREETEXT(pub_id, ' "0736*" ')
Yes, you can search as many columns as necessary, but be aware that each FTS
predicate (CONTAINS* or FREETEXT*) is a "round-trip" to the FT Catalog and
depending upon the size of your FT Catalog (based upon the number of rows in
your FT enable table as well as the number of non-noise unique words), your
FTS query performance may be affected.
Regards,
John
"Bruce" <Bruce@.discussions.microsoft.com> wrote in message
news:BD072BA9-6A90-4C72-B61F-35DC691EC044@.microsoft.com...
> This is the Query I am using right now,How do I add one more column to
this.
> I dont want to use mutlple stored procedures.
> SELECT *
> FROM news AS FT_TBL INNER JOIN
> FREETEXTTABLE(news,news_summary,'FORMSOF(INFLECTIO NAL, "Report")') AS
KEY_TBL[vbcol=seagreen]
> ON FT_TBL.news_id= KEY_TBL.[KEY]
> WHERE(
> expire_dtm>getdate()
> )
> "Bruce" wrote:
one[vbcol=seagreen]
sql

full text search on multiple tables yielding one rank

Is it possible to do full text search on multiple tables and having one rank
for all searches? Ive read a lot of articles that gives me code samples on
doing FTS on multi tables but they gave me multiple ranks for each search.
Without consolidating all of your data in the multiple tables into a child
table there is no way to do this with a meaningful rank.
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
"surigaonon" <surigaonon@.discussions.microsoft.com> wrote in message
news:28625427-A482-40B3-991B-BC7C35501801@.microsoft.com...
> Is it possible to do full text search on multiple tables and having one
> rank
> for all searches? Ive read a lot of articles that gives me code samples on
> doing FTS on multi tables but they gave me multiple ranks for each search.
sql

Full text search

Hello,
Can anybody explain me why is it showing rank as 0 always in the result?
Table Data:
LLBPSEPG
This is test for the Search
search test for heat
mango apple banana
eating banana is good for health
man eats rice healthy
big laod in the ship hot
SPEG App LLBP - Next LLBP
Lesson learned are from mistakes
good apples are tasty dry
mangos are too costly dried
it is time for starting the project
Query:
SELECT LLBPSEPG
FROM T_TM_LLBPData a, FREETEXTTABLE(T_TM_LLBPData, LLBPSEPG, 'apple banana
mango ') b
WHERE a.LLBPNo = b.[key]
Output:
LLBPSEPG Rank
mangos are too costly dried 0
eating banana is good for health 0
mango apple banana 0
good apples are tasty dry 0
thanks and regards
SathianSathian,
Is the text below "Table data" and for column "LLBPSEPG" the actual text and
number of rows (1) in your table "T_TM_LLBPData"? If so, then this is too
small a table to get correct RANK values. Specifically, RANK requires a
statistically significant number of rows (usually >10,000) and a
corresponding number of unique non-noise words to get correct RANK values
returned. See SQL Server 2000 BOL title "Full-text Search Recommendations"
for more information on RANK. Additionally, your FREETEXTTABLE query is
searching for the phrase "apple banana mango" and while no one row has this
exact phrase, you did get results for variations of these words including
"apples" for your query specifying "apple". You might want to try searching
on the phrase "mango apple banana" as there is an exact match for this
phrase in your small table's data.
Hope that helps!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sathian T" <sathian.t@.in.Bosch.com> wrote in message
news:cufqjk$g8h$1@.ns2.fe.internet.bosch.com...
> Hello,
> Can anybody explain me why is it showing rank as 0 always in the result?
>
> Table Data:
> LLBPSEPG
> This is test for the Search
> search test for heat
> mango apple banana
> eating banana is good for health
> man eats rice healthy
> big laod in the ship hot
> SPEG App LLBP - Next LLBP
> Lesson learned are from mistakes
> good apples are tasty dry
> mangos are too costly dried
> it is time for starting the project
>
> Query:
> SELECT LLBPSEPG
> FROM T_TM_LLBPData a, FREETEXTTABLE(T_TM_LLBPData, LLBPSEPG, 'apple banana
> mango ') b
> WHERE a.LLBPNo = b.[key]
> Output:
> LLBPSEPG Rank
> mangos are too costly dried 0
> eating banana is good for health 0
> mango apple banana 0
> good apples are tasty dry 0
>
> thanks and regards
> Sathian
>
>|||Dear Friends,
Do any of you have an answer for this?
Regards
Sathian|||Sathian,
Is the text below "Table data" and for column "LLBPSEPG" the actual text and
number of rows (1) in your table "T_TM_LLBPData"? If so, then this is too
small a table to get correct RANK values. Specifically, RANK requires a
statistically significant number of rows (usually >10,000) and a
corresponding number of unique non-noise words to get correct RANK values
returned. See SQL Server 2000 BOL title "Full-text Search Recommendations"
for more information on RANK. Additionally, your FREETEXTTABLE query is
searching for the phrase "apple banana mango" and while no one row has this
exact phrase, you did get results for variations of these words including
"apples" for your query specifying "apple". You might want to try searching
on the phrase "mango apple banana" as there is an exact match for this
phrase in your small table's data.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sathian T" <sathian.t@.in.Bosch.com> wrote in message
news:cv18fk$bqn$1@.ns2.fe.internet.bosch.com...
> Dear Friends,
> Do any of you have an answer for this?
> Regards
> Sathian
>
>

Full Text ContainsTable does not return desired results

Hi -
I did the following SQL Query.
set @.sql = @.sql + 'select distinct
s.PrimaryKey,
indx.rank, d.documenttype_id,
d.publish_date, d.Document_ID, d.Document_name, d.Description,
d.create_date
,isnull(f.mimetype,'''') as [mimetype]
,isnull(f.filesize,0) as [filesize]
--,dbo.fn_GetBreadCrumbHtml(d.Document_ID) as [breadcrumb]
,'''' as [breadcrumb]
from SearchItem s Join
containstable(SearchItem, *, ''' + @.p_keywords + ''') '+
'indx on s.SearchItem_Id = indx.[key]
JOIN document d on s.primarykey = d.document_id'
However, when I set the @.p_keywords = "ACLU" or "ability to pay" or
"credibility determination" - I get back relevant results but not every
single document that contains the keywords. I checked the documents
that are not returned and they exist in the full text table & do
contains the exact keywords.
Thanks in advance. -Aarti.
My config:
us_english
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000
00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Enterprise
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
can you tell me anything about the nature of the data in the table? Is any
of it in the image column? If so what is the document type?
Also what happens if you uncomment the
'indx on s.SearchItem_Id = indx.[key]
and comment the
JOIN document d on s.primarykey = d.document_id'
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
<aartinawani@.hotmail.com> wrote in message
news:1119290688.908083.251200@.g14g2000cwa.googlegr oups.com...
> Hi -
> I did the following SQL Query.
> set @.sql = @.sql + 'select distinct
> s.PrimaryKey,
> indx.rank, d.documenttype_id,
> d.publish_date, d.Document_ID, d.Document_name, d.Description,
> d.create_date
> ,isnull(f.mimetype,'''') as [mimetype]
> ,isnull(f.filesize,0) as [filesize]
> --,dbo.fn_GetBreadCrumbHtml(d.Document_ID) as [breadcrumb]
> ,'''' as [breadcrumb]
> from SearchItem s Join
> containstable(SearchItem, *, ''' + @.p_keywords + ''') '+
> 'indx on s.SearchItem_Id = indx.[key]
> JOIN document d on s.primarykey = d.document_id'
> However, when I set the @.p_keywords = "ACLU" or "ability to pay" or
> "credibility determination" - I get back relevant results but not every
> single document that contains the keywords. I checked the documents
> that are not returned and they exist in the full text table & do
> contains the exact keywords.
> Thanks in advance. -Aarti.
> My config:
> us_english
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000
> 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Enterprise
> Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>
|||Thanks for your response. We are indexing two columns one image (document
type = pdf) and one ntext.
This line is not commented - 'indx on s.SearchItem_Id = indx.[key]
It's just a sql string I put together and execute at the end of the stored
proc.
This line - JOIN document d on s.primarykey = d.document_id : cannot be
commented out because the where clause is dependent on the document table. I
sql query I posted was just a sample not the entire query. Please let me know
if you need any more information and thanks again for your response.
"Hilary Cotter" wrote:

> can you tell me anything about the nature of the data in the table? Is any
> of it in the image column? If so what is the document type?
> Also what happens if you uncomment the
> 'indx on s.SearchItem_Id = indx.[key]
> and comment the
> JOIN document d on s.primarykey = d.document_id'
>
> --
> 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
> <aartinawani@.hotmail.com> wrote in message
> news:1119290688.908083.251200@.g14g2000cwa.googlegr oups.com...
>
>