ASP.NET and SQL 2000 FULL Text Search Results

[dave]

I am wondering if the following can be done.

I want to setup a search page that utilizes full text
searching in sql2000. I want the user to type in
say "where is bill" and have the query search across
multiple columns in multiple tables and return a single
list of results so that i can show unique records that
are returned.
The problem is that my data for 1 record is split across
multiple tables so that the search has to occur in each
table but still only return unique/distinct records.
I hope this makes sense.
Can anyone help on how i can accomplish this?
thank you in advance
dave

[Dominique Kuster]

"dave" <[e-mail]> wrote in message
news:02****************************@phx.gbl...

I am wondering if the following can be done.

I want to setup a search page that utilizes full text
searching in sql2000. I want the user to type in
say "where is bill" and have the query search across
multiple columns in multiple tables and return a single
list of results so that i can show unique records that
are returned.
The problem is that my data for 1 record is split across
multiple tables so that the search has to occur in each
table but still only return unique/distinct records.
I hope this makes sense.
Can anyone help on how i can accomplish this?
thank you in advance
dave

It could be a candidate for MSSqlServer English Query.
http://www.microsoft.com/sql/evaluat...es/english.asp
"With English Query, the user can create applications that accept
natural-language queries, questions written in plain English, instead of
complex SQL queries."
I don't know this technology but I would be interested to know if it applies
to this case.

[Tian Min Huang]

Hello Dave,

Thanks for your post. I suggest that you can use Full-Text Search for each
table, and then combine the result programmatically. I believe the
following MSDN documentations are helpful:

Building Search Applications for the Web Using Microsoft SQL Server 2000
Full-Text Search
http://msdn.microsoft.com/library/de...us/dnsql2k/htm
l/sql_fulltextsearch.asp

Full-Text Search Deployment
http://support.microsoft.com/default...ntent/2000pape
rs/fts_white%20paper.asp#IndexC

SQL Server 2000 Books Online
http://www.microsoft.com/sql/techinf...2000/books.asp

Hope this helps.

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! ¨C www.microsoft.com/security
This posting is provided ¡°as is¡± with no warranties and confers no rights.

[Vikrant V Dalwale [MS]]

Hello Dave,

You are right about AND operator in CONTAINS and CONTAINSTABLE Predicate
where <Search Condition1> AND <Search Condition2>
will be TRUE only if both conditions are in the same column mentioned in
the predicate. However if you mention * insead of the column name
then search condition with AND operator applies to all the columns which
are candidates for FTS.

However, for FREETEXT AND FREETEXTABLE , the <free text string> does not
recognize AND as a boolean operator and you can't use it.

For AND operation where search conditions in the same expression exists in
diff columns of the table, you need to implement it using a Sql Query.

E.G,

USE NORTHWIND
GO
SELECT ProductName
FROM Products
WHERE UnitPrice = 15.00
AND CONTAINS(QuantityPerUnit, 'boxes')
AND CONTAINS(ProductName, ' "choc*" ')
GO

Please let me know if that answers your question.

Thanks for posting to MSDN Managed Newsgroup.

Thanks,

Vikrant Dalwale

Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.

--------------------

Content-Class: urn:content-classes:message
From: "dave" <[e-mail]>
Sender: "dave" <[e-mail]>
References: <02****************************@phx.gbl> <F3**************@cpmsftngxa06.phx.gbl>Subject: RE: ASP.NET and SQL 2000 FULL Text Search Results
Date: Fri, 29 Aug 2003 09:46:56 -0700
Lines: 11
Message-ID: <01****************************@phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Thread-Index: AcNuTShlfeGHyHVSRxi87UgM86a6xw==
Newsgroups: microsoft.public.dotnet.general
Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.general:106614
NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
X-Tomcat-NG: microsoft.public.dotnet.general

I read the following somewhere on the web...

For Contains and ContainsTable you are correct. The
boolean AND will only work when both hits are in the same
column. For FreeText and FreeTextTable the query and
boolean operators can look across columns.

Is the something wrong with the above approach?
thx
dave

[Vikrant V Dalwale [MS]]

Hello Dave,

Please let us know if you need any further assistance on this issue.

Thanks for posting to MSDN Managed Newsgroup.

Vikrant Dalwale

Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.

--------------------

Content-Class: urn:content-classes:message
From: "dave" <[e-mail]>
Sender: "dave" <[e-mail]>
Subject: ASP.NET and SQL 2000 FULL Text Search Results
Date: Thu, 28 Aug 2003 09:30:46 -0700
Lines: 15
Message-ID: <02****************************@phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Thread-Index: AcNtgbvzzGwymmJQSSSulVTwnwBngw==
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Newsgroups: microsoft.public.dotnet.general
Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.general:106423
NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
X-Tomcat-NG: microsoft.public.dotnet.general

I am wondering if the following can be done.

I want to setup a search page that utilizes full text
searching in sql2000. I want the user to type in
say "where is bill" and have the query search across
multiple columns in multiple tables and return a single
list of results so that i can show unique records that
are returned.
The problem is that my data for 1 record is split across
multiple tables so that the search has to occur in each
table but still only return unique/distinct records.
I hope this makes sense.
Can anyone help on how i can accomplish this?
thank you in advance
dave