Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Full-Text Search / April 2007

Tip: Looking for answers? Try searching our database.

contains query

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
coffej - 24 Apr 2007 19:23 GMT
I have been trying to get the following to work:

(select p.uid, p.title, (DATENAME(MONTH,p.newDate) + ' ' +
DATENAME(DAY,p.newDate) + ', ' + DATENAME(YEAR, p.newDate) ) as Date,
p.tableName from authorsPubs AP, publications p, authors a where
AP.pubID = p.uid and AP.authorID = a.author_ID and p.display = 1 AND
( contains(a.name, ' ''rea'' OR ''akashi'' ' )) UNION select
FT_TBL2.libraryid as id, FT_TBL2.articleTitle, FT_TBL2.dateofPub as
date, FT_TBL2.tableName from resourceLibrary as FT_TBL2 where
( FT_TBL2.display = 1 ) AND ( contains(FT_TBL2.authoranalytic, '
''rea'' OR ''akashi'' ' )) OR ( contains(FT_TBL2.authormonographic, '
''rea'' OR ''akashi'' ' )) OR ( contains(FT_TBL2.authorsubsidiary, '
''rea'' OR ''akashi'' ' )) ) order by p.title

I have used Microsoft's parseIt function and another function to clean
up the ignored words and a 3rd function to build the query - the
function build the query works properly but how can I handle if a user
wants to enter an exact match by using double quotes around their
"search terms"

Thank you for any assistance - I have been looking at this way too long
Hilary Cotter - 25 Apr 2007 15:35 GMT
try

select p.uid, p.title, (DATENAME(MONTH,p.newDate) + ' ' +

DATENAME(DAY,p.newDate) + ', ' + DATENAME(YEAR, p.newDate) ) as Date,

p.tableName from authorsPubs AP join publications p on AP.pubID = p.uid

join authors a on AP.authorID = a.author_ID

where

p.display = 1 AND contains(a.name, ' ''rea'' OR ''akashi'' ' )

UNION all

select FT_TBL2.libraryid as id, FT_TBL2.articleTitle, FT_TBL2.dateofPub as

date, FT_TBL2.tableName from resourceLibrary as FT_TBL2 where

( FT_TBL2.display = 1 ) AND contains(FT_TBL2.*, '

''rea'' OR ''akashi'' ' ) order by p.title

This will work if  authoranalytic, authormonographic,  and authorsubsidiary
are the only full-text indexed columns in resource library.

You shoudl have indexes on resourcelibrary.display and publisher.display

Also is there a relationship between resource libary and any of the other
tables?

>I have been trying to get the following to work:
>
[quoted text clipped - 17 lines]
>
> Thank you for any assistance - I have been looking at this way too long
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.