Full text fields search with excluded word

[chosenones]

I have a list of available tapes with the following fields:
ID, Date, Service, Speaker, Text, Title, Subject

I would like to do a full text search on title and subject with multiple words. The first word must be found, each following word may have the following prefixes:
No prefix: the first word OR this word
+: the first word AND this word
-: the first wrod but NOT this word

Currently for the search "word1 -word2" I generate:

SELECT * FROM Data WHERE ((Subject LIKE('%word1%') OR Title LIKE('%word1%')) AND NOT (Subject LIKE('%word2%') OR Title LIKE('%word2%')))

but this removes too many records!

I am using Access ODBC driver in Java (not Access program)

 

[jjob]

I am assuming that by 'removes too many records', you mean it removes some that you don't expect should be removed, could you give some precise examples of the data being removed that should not be?

 

[chosenones]

Look at this as HTML. These are the results for a search on "grace":

<table border=1><tr><td>143</td><td>1999-06-30 00:00:00.0</td><td>Wed.</td><td>Pastor Alvis</td><td>Josh. 2:1-14</td><td>God's Far-Reaching Grace</td><td>Grace</td></tr>
<tr><td>178</td><td>1999-09-12 00:00:00.0</td><td>A.M.</td><td>Pastor Alvis</td><td>James 4:6</td><td>Grace or Disgrace? - Part 1</td><td></td></tr>
<tr><td>179</td><td>1999-09-12 00:00:00.0</td><td>P.M.</td><td>Pastor Alvis</td><td>Titus 2:11-15</td><td>Grace or Disgrace? - Part 2</td><td></td></tr>
<tr><td>264</td><td>2000-02-27 00:00:00.0</td><td>A.M.</td><td>Pastor Alvis</td><td>II Cor. 8:1-15</td><td>Grace Giving</td><td></td></tr>
<tr><td>554</td><td>2001-07-25 00:00:00.0</td><td>Wed.</td><td>Pastor Alvis</td><td>Psalm 16</td><td>God's Grace - Active in My Life</td><td></td></tr>
<tr><td>830</td><td>2002-11-17 00:00:00.0</td><td>A.M.</td><td>Pastor Alvis</td><td>James 4:7-10</td><td>Choosing to be a Recipient of God's Grace</td><td></td></tr>
<tr><td>908</td><td>2003-03-30 00:00:00.0</td><td>A.M.</td><td>Pastor Alvis</td><td>I Peter 1:13-16</td><td>Grace - Living in the Marketplace</td><td></td></tr>
<tr><td>928</td><td>2003-05-04 00:00:00.0</td><td>A.M.</td><td>Pastor Alvis</td><td>I Peter 2:11-17</td><td>Grace - Relations With Others</td><td></td></tr>
<tr><td>960</td><td>2003-07-06 00:00:00.0</td><td>P.M.</td><td>Pastor Alvis</td><td>Genesis 6:4-8</td><td>Grace Amidst Wickedness</td><td></td></tr>
<tr><td>971</td><td>2003-08-03 00:00:00.0</td><td>A.M.</td><td>Pastor Alvis</td><td>I Peter 4:1-6</td><td>Living as Good Stewards of God's Grace I</td><td></td></tr>
<tr><td>972</td><td>2003-08-03 00:00:00.0</td><td>P.M.</td><td>Pastor Alvis</td><td>Genesis 8</td><td>God's Grace on Display, Again</td><td></td></tr>
<tr><td>974</td><td>2003-08-10 00:00:00.0</td><td>A.M.</td><td>Pastor Alvis</td><td>I Peter 4:7-11</td><td>Living as Good Stewards of God's Grace II</td><td></td></tr>
<tr><td>980</td><td>2003-08-24 00:00:00.0</td><td>A.M.</td><td>Pastor Alvis</td><td>Various</td><td>Basics for Believers - Understanding Grace</td><td>Grace Part 1</td></tr>
<tr><td>981</td><td>2003-08-24 00:00:00.0</td><td>P.M.</td><td>Pastor Alvis</td><td>Various</td><td>Basics for Believers - Understanding Grace</td><td>Grace Part 2</td></tr>
<tr><td colspan=7>14 Records Returned</td></tr>
</table>

These are results for &quot;grace -giving&quot;:

<table border=1><tr><td>143</td><td>1999-06-30 00:00:00.0</td><td>Wed.</td><td>Pastor Alvis</td><td>Josh. 2:1-14</td><td>God's Far-Reaching Grace</td><td>Grace</td></tr>
<tr><td>980</td><td>2003-08-24 00:00:00.0</td><td>A.M.</td><td>Pastor Alvis</td><td>Various</td><td>Basics for Believers - Understanding Grace</td><td>Grace Part 1</td></tr>
<tr><td>981</td><td>2003-08-24 00:00:00.0</td><td>P.M.</td><td>Pastor Alvis</td><td>Various</td><td>Basics for Believers - Understanding Grace</td><td>Grace Part 2</td></tr>
<tr><td colspan=7>3 Records Returned</td></tr>
</table>


I only see one record with &quot;giving&quot; in it in the first search, but 11 other records are not returned. ??!!!

 

[jjob]

TVM, thats useful, although knowing the precise SQL that gave the results and the database column names would help, however I think the clue may lie in the 'last' table column that has the 'Grace' and 'Grace Part 1' and 'Grace Part 2' in it. It looks to me as though it is Null - certainly the table cells are empty for all those that do not appear once you use the -giving.

I suggest you look there and check the precise SQL statement that you are using.

regards

John

 

[jjob]

Further to my last post, I realised that I haven't read your post carefully enough - you did give me the field names as well as the SQL, my apologies for suggesting otherwise. The answer however is still, I believe, the existence of Null in the Subject field, as Null is an odd thing, and it propagates through expressions , so I think you will need to check for not nulls in the NOT part of your SQL.

 

[chosenones]

Added an &quot;IS NOT NULL&quot; to query for each field and it WORKS!
Thank you!

 

[jjob]

My pleasure!