I was recently working with someone on a similar project. I set up four test cases, and I can think
of several more to try. We had + words and - words (must-have, must-not-have).

Select ... Where ... And Exists (each + word) ... and Not Exists (any of the - words)

Select sum(one_word.word_count) ... Inner Join (any of the + words) ... Left Join (any of the -
words) ... Having (positive count for all + words, 0 count for all - words)

Select distinct ... Inner Join (each + word) ... Where Not Exists (any of the - words)

[another method involved selecting all documents, and all counts of words for all words involved,
then doing an after-the-fact clean-up in which documents with - words were removed, and documents
that didn't have at least one of each of the + words were removed as well.]

Something I did the other day involved a query like ...

select count(select distinct things from somewhere where things in (list of things i want)), ...
group by ... having (that same count) = number of things in the list that i wanted. That made sure
i had each of the things i wanted, without any consequences if there were more.

I haven't tested that against a larger database to see how quickly it runs, and I haven't tried
doing it with both + and - entries. You could easily enough add a 'where not exists' or somesuch.

-Philip


---------- Original Message -----------
From: Bob Murdoch <ram5@...>

> Ian,
>
> That's an interesting strategy. I'll give it a try, and see how
> scalable it is.
>
> Bob M..
>
> At 4/19/2004 09:27 AM, Ian A. Newby wrote:
> >Hi Bob,
> >
> >try,
> >
> > select
> > c.article
> > from
> > content c
> > join keyword k1 on (c.content_id = k1.content_id)
> > join keyword k2 on (c.content_id = k2.content_id)
> > join keyword k3 on (c.content_id = k3.content_id)
> > where
> > k1.word = 'firebird' and
> > k2.word = 'database' and
> > k3.word = 'search'
> >
> >Regards
> > Ian Newby

------- End of Original Message -------