contains query

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
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?
"coffej" <coffej@.rpi.edu> wrote in message
news:1177439026.023441.33740@.c18g2000prb.googlegro ups.com...

>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
>