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
>

Constraint between sysusers table and a user defined table

I want to create a user defined table with Foreign Key constrained to uid of
sysusers table. How can this constraint be enforced?
I am using Server Authentication on SQL Server 2000 v8.
Thank you for your assistance.I think you will need to use a trigger or self-coded "preventative" measures
because you cannot set up a constraint against a system table.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"wolfv" <wolfv@.discussions.microsoft.com> wrote in message
news:54518189-345A-47BF-B990-9ADAA03036C0@.microsoft.com...
>I want to create a user defined table with Foreign Key constrained to uid
>of
> sysusers table. How can this constraint be enforced?
> I am using Server Authentication on SQL Server 2000 v8.
> Thank you for your assistance.
>|||wolf
Why?Please explain what is your purpose?
"wolfv" <wolfv@.discussions.microsoft.com> wrote in message
news:54518189-345A-47BF-B990-9ADAA03036C0@.microsoft.com...
> I want to create a user defined table with Foreign Key constrained to uid
of
> sysusers table. How can this constraint be enforced?
> I am using Server Authentication on SQL Server 2000 v8.
> Thank you for your assistance.
>|||Hi
FK's can not span databases.
You need to enforce this through a trigger.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"wolfv" wrote:

> I want to create a user defined table with Foreign Key constrained to uid
of
> sysusers table. How can this constraint be enforced?
> I am using Server Authentication on SQL Server 2000 v8.
> Thank you for your assistance.
>|||The purpose of the FK constraint (which is not allowed) is to prevent orphan
s
in the userdefined table when someone attempts to delete a user from the
sysusers table.
"Uri Dimant" wrote:

> wolf
> Why?Please explain what is your purpose?
>
> "wolfv" <wolfv@.discussions.microsoft.com> wrote in message
> news:54518189-345A-47BF-B990-9ADAA03036C0@.microsoft.com...
> of
>
>|||
"Uri Dimant" wrote:

> wolf
> Why?Please explain what is your purpose?
>
> "wolfv" <wolfv@.discussions.microsoft.com> wrote in message
> news:54518189-345A-47BF-B990-9ADAA03036C0@.microsoft.com...
> of
>
>|||The sysusers table and the user-defined table reside in the same database.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> FK's can not span databases.
> You need to enforce this through a trigger.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "wolfv" wrote:
>|||Yes, I see what you mean. I found a place in SQL Server Manual that says
constraints against a system table are not permitted.
Thank you.
"AB - MVP" wrote:

> I think you will need to use a trigger or self-coded "preventative" measur
es
> because you cannot set up a constraint against a system table.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "wolfv" <wolfv@.discussions.microsoft.com> wrote in message
> news:54518189-345A-47BF-B990-9ADAA03036C0@.microsoft.com...
>
>