"mode" in it. Isn't the keyword CONTAINS supposed to treat my search
expression as one word? Can someone show me what is wrong with this query so
that it returns only records that have the exact search expression "mode-4"
in it? Thank you.
SELECT MyFields
FROM MyTable M
LEFT JOIN Table1 T1 ON T1.Field1 = M.Field1
LEFT JOIN Table2 T2 ON T2.Field1 = M.Field2
LEFT JOIN Table3 T3 ON T3.Field1 = M.Field3
LEFT JOIN Table4 T4 ON T4.Field1 = M.Field4
WHERE CONTAINS( M.* , '"mode-4"' ) ORDER BY M.Field1
are the fields in Table1, Table2, Table3, Table4 and MyFields fulltext
indexed or are they integer values?
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
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:82826E0A-C56B-4B47-B811-95F8F7075530@.microsoft.com...
>I am running the below query and getting back results that have the word
> "mode" in it. Isn't the keyword CONTAINS supposed to treat my search
> expression as one word? Can someone show me what is wrong with this query
> so
> that it returns only records that have the exact search expression
> "mode-4"
> in it? Thank you.
> SELECT MyFields
> FROM MyTable M
> LEFT JOIN Table1 T1 ON T1.Field1 = M.Field1
> LEFT JOIN Table2 T2 ON T2.Field1 = M.Field2
> LEFT JOIN Table3 T3 ON T3.Field1 = M.Field3
> LEFT JOIN Table4 T4 ON T4.Field1 = M.Field4
> WHERE CONTAINS( M.* , '"mode-4"' ) ORDER BY M.Field1
|||Not sure what you mean by "are they integer values", but the table that
contains MyFields is full-text indexed. Do tables 1,2,3, and 4 need to be
full-text indexed? Course, I'm thinking yes since you asked the question
"Hilary Cotter" wrote:
> are the fields in Table1, Table2, Table3, Table4 and MyFields fulltext
> indexed or are they integer values?
> --
> 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
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:82826E0A-C56B-4B47-B811-95F8F7075530@.microsoft.com...
>
>
|||Perhaps if you could post the schema. For the record mode-4 is indexed and
queried two separate words. If 4 is not in your noise word list this should
work.
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
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:E3738AD4-7A01-40C2-95DB-A968D423B247@.microsoft.com...[vbcol=seagreen]
> Not sure what you mean by "are they integer values", but the table that
> contains MyFields is full-text indexed. Do tables 1,2,3, and 4 need to be
> full-text indexed? Course, I'm thinking yes since you asked the question
>
> "Hilary Cotter" wrote:
|||Sorry, but our company specifically prohibits posting any schema details in
newsgroups, but should the order be:
1. Remove words from the noise list.
2. Create the full-text index.
I'm wondering that since I created the index before removing the 4 from the
noise list, that it may be the reason my search is not working.
"Hilary Cotter" wrote:
> Perhaps if you could post the schema. For the record mode-4 is indexed and
> queried two separate words. If 4 is not in your noise word list this should
> work.
> --
> 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
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:E3738AD4-7A01-40C2-95DB-A968D423B247@.microsoft.com...
>
>
|||OK, let me guess your schema then from what you have posted.
Create MyFields(PK int not null identity primary key, Fields1 char(20),
Field2 char(20), Field3 char(20), Field4 char(20))
Create T1 (pk int not null references MyFields(PK), Field1 char(20))
Create T2 (pk int not null references MyFields(PK), Field2 char(20))
Create T3 (pk int not null references MyFields(PK), Field3 char(20))
Create T4 (pk int not null references MyFields(PK), Field4 char(20))
This is kind of critical as I think your join condition is all wrong.
But you are correct with a search on mode-4 and you have removed 4 from your
noise word list after building your index you will not get correct results.
In fact you should get fewer results which makes me wonder about your join
condition.
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
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:15E5FCB3-0A36-4B0E-97D0-C0E62376B759@.microsoft.com...[vbcol=seagreen]
> Sorry, but our company specifically prohibits posting any schema details
> in
> newsgroups, but should the order be:
> 1. Remove words from the noise list.
> 2. Create the full-text index.
> I'm wondering that since I created the index before removing the 4 from
> the
> noise list, that it may be the reason my search is not working.
> "Hilary Cotter" wrote:
|||The join condition is there because there are many other items that we are
building a where clause on. I simplified the query to what I thought was most
necessary and only included the joins to be true to my actual query, but with
what you have said about creating the full-text after removing the number 4
from the noise file, now I do not think they matter. If I could show you the
actual schema, I think you would agree with me.
Thank you very much for your time. I'll recreate the full-text index, after
removing any noise words, and see how it works for me then.
"Hilary Cotter" wrote:
> OK, let me guess your schema then from what you have posted.
> Create MyFields(PK int not null identity primary key, Fields1 char(20),
> Field2 char(20), Field3 char(20), Field4 char(20))
> Create T1 (pk int not null references MyFields(PK), Field1 char(20))
> Create T2 (pk int not null references MyFields(PK), Field2 char(20))
> Create T3 (pk int not null references MyFields(PK), Field3 char(20))
> Create T4 (pk int not null references MyFields(PK), Field4 char(20))
> This is kind of critical as I think your join condition is all wrong.
> But you are correct with a search on mode-4 and you have removed 4 from your
> noise word list after building your index you will not get correct results.
> In fact you should get fewer results which makes me wonder about your join
> condition.
> --
> 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
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:15E5FCB3-0A36-4B0E-97D0-C0E62376B759@.microsoft.com...
>
>
|||MS changed the worbreaker in windows 2003, to contain what I consider
is now a bug.
The hyphen in 'mode-4' is actually now used to split the phrase into 2
words, therefore doing an OR search, hence returning every record with
'mode' OR '4' in it, which I expect will be quite a few.
On windows 2000, this worked properly to join the word, as a hyphen is
actually supposed to in text.
Eventually I got around it by replacing all hyphens in the indexed
text with HYP ie 'modeHYP4'. You need to replace any hyphens that
users enter in the search to the same.
Lots of our product skus had hyphens in so it was causing all sorts of
problems, 21-500 was returning thousands of results instead of 1.
Hope this helps...
On 14 Feb, 19:35, Mike Collins <MikeColl...@.discussions.microsoft.com>
wrote:
> The join condition is there because there are many other items that we are
> building a where clause on. I simplified the query to what I thought was most
> necessary and only included the joins to be true to my actual query, but with
> what you have said about creating the full-text after removing the number 4
> from the noise file, now I do not think they matter. If I could show you the
> actual schema, I think you would agree with me.
No comments:
Post a Comment