Help with returning too much data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike Collins - 13 Feb 2007 21:09 GMT

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

Reply to this Message

Hilary Cotter - 14 Feb 2007 02:33 GMT

are the fields in Table1, Table2, Table3, Table4 and MyFields fulltext
indexed or are they integer values?

Signature

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

>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
[quoted text clipped - 11 lines]
> LEFT JOIN Table4 T4 ON T4.Field1 = M.Field4
> WHERE CONTAINS( M.* , '"mode-4"' ) ORDER BY M.Field1

Reply to this Message

Mike Collins - 14 Feb 2007 13:52 GMT

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

> are the fields in Table1, Table2, Table3, Table4 and MyFields fulltext
> indexed or are they integer values?
[quoted text clipped - 14 lines]
> > LEFT JOIN Table4 T4 ON T4.Field1 = M.Field4
> > WHERE CONTAINS( M.* , '"mode-4"' ) ORDER BY M.Field1

Reply to this Message

Hilary Cotter - 14 Feb 2007 15:07 GMT

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.

Signature

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

> 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
[quoted text clipped - 20 lines]
>> > LEFT JOIN Table4 T4 ON T4.Field1 = M.Field4
>> > WHERE CONTAINS( M.* , '"mode-4"' ) ORDER BY M.Field1

Reply to this Message

Mike Collins - 14 Feb 2007 15:40 GMT

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.

> 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
[quoted text clipped - 24 lines]
> >> > LEFT JOIN Table4 T4 ON T4.Field1 = M.Field4
> >> > WHERE CONTAINS( M.* , '"mode-4"' ) ORDER BY M.Field1

Reply to this Message

Hilary Cotter - 14 Feb 2007 17:15 GMT

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.

Signature

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

> Sorry, but our company specifically prohibits posting any schema details
> in
[quoted text clipped - 39 lines]
>> >> > LEFT JOIN Table4 T4 ON T4.Field1 = M.Field4
>> >> > WHERE CONTAINS( M.* , '"mode-4"' ) ORDER BY M.Field1

Reply to this Message

Mike Collins - 14 Feb 2007 19:35 GMT

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.

> OK, let me guess your schema then from what you have posted.
>
[quoted text clipped - 55 lines]
> >> >> > LEFT JOIN Table4 T4 ON T4.Field1 = M.Field4
> >> >> > WHERE CONTAINS( M.* , '"mode-4"' ) ORDER BY M.Field1

Reply to this Message

Martin - 20 Feb 2007 17:05 GMT

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.

Reply to this Message