1

Sorry for the bad post title but I couldn't summarize this better.

It's better to use an example. Say I have this simple table with two text columns (I'm leaving the other columns out).

Id    Text_1  Text_2
1     a       a b
2     c       a b

Now if I want to search for '"a" and not "b"', in my current implementation I'm getting record 1 back. I understand why this is, it's because the search condition is a match on column "Text_1", while for record 2 it's not a match on any column.

However, for the end user this may not be intuitive, as they probably mean to exclude record 1 as well most of the time.

So my question is, if I want to tell SQL Server to do the matching "across all columns" (meaning that if the "NOT" portion is found on ANY column, the record shouldn't match), is it possible?

EDIT: This is what my query would look like for this example:

SELECT Id, TextHits.RANK Rank, Text_1, Text_2 FROM simple_table
JOIN CONTAINSTABLE(simple_table, (Text_1, Text_2), '"a" and not "b"') TextHits
ON TextHits.[KEY] = simple_table.Id
ORDER BY Rank DESC

The actual query is a bit more complicated (more columns, more joins, etc) but this is the general idea :)

Thanks!

xurumanga
  • 45
  • 6
  • 2
    @xurumunga . . . Can you provide an example of the current code you are using that you don't think does the right thing? Also, including the database as a tag is important, because different databases have different strengths for searching text. – Gordon Linoff Apr 01 '13 at 14:27
  • Thanks @Gordon for the suggestions. I added the query and tag. – xurumanga Apr 01 '13 at 15:10
  • there are a million different examples and this is a duplicate of a duplicate it seems: http://stackoverflow.com/questions/1796506/search-all-tables-all-columns-for-a-specific-value-sql-server – RandomUs1r Apr 01 '13 at 15:31
  • @RandomUs1r I don't see how that question is related to this one. Could you explain please? – xurumanga Apr 01 '13 at 15:52
  • 1
    use one of those scripts to find the value in any column and then use an outer query to match against what's returned and exclude it. – RandomUs1r Apr 01 '13 at 16:08
  • Thanks but that's not really my use case. The set of columns I want to search is well known, I don't want to search everywhere. Also, and I should have said this before, I need to support much more than this. It's not just '"a" and not "b"'. The search page supports And, Or, Exact phrase match, Thesaurus and Word inflections. Doing a post-check for the exclude term (whether through code or by having another join with a sub-query) is very specific to this one example and it doesn't sound like it will scale very well to the full functionality. My bad for not mentioning this before. – xurumanga Apr 01 '13 at 16:45
  • It could work though. I'll have to think it through. – xurumanga Apr 01 '13 at 16:46

1 Answers1

1

The logic is going to be evaluated against each record so if you want an exclusion hit from one record in a row to cause an exclusion on the row you should use a NOT EXISTS and break out the fullText query into separate inclusionary and exclusionary parts...

SELECT  Id, 
        TextHits.RANK Rank, 
        Text_1, 
        Text_2 
FROM    simple_table
JOIN    CONTAINSTABLE(simple_table, (Text_1, Text_2), '"a"') TextHits
        ON  TextHits.[KEY] = simple_table.Id
WHERE   NOT EXISTS (SELECT  1
                    FROM    CONTAINSTABLE(simple_table, (Text_1, Text_2), '"b"') exclHits
                    WHERE   TextHits.[KEY] = exclHits.[KEY])
ORDER BY Rank DESC
Eric J. Price
  • 2,740
  • 1
  • 15
  • 21
  • Thanks @Love2Learn. Like RandomUs1r also suggested, it seems that doing that post step to exclude is what I need to do. I'll just have to think it through because I need to support more than '"a" and not "b"' and I didn't want to over-complicate my query. I'm hoping it'll work though. I'll post back when I get a chance to try it. – xurumanga Apr 01 '13 at 16:52
  • This approach did the trick. We ended up with something slightly different but pretty much the same; instead of doing WHERE NOT EXISTS we ended up with WHERE Id NOT IN (SELECT exclHits.[KEY] FROM CONTAINSTABLE (...)). Cheers all :) – xurumanga Apr 02 '13 at 09:51
  • From a query plan standpoint you could also do the `NOT EXISTS` and just change the `WHERE TextHits.[KEY] = exclHits.[KEY]` to 'WHERE simple_table.Id = exclHits.[KEY]`, but from a query plan standpoint it should be the same thing. Glad it worked out for you. – Eric J. Price Apr 02 '13 at 18:41