"AK" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1123962995.230752.97530@g14g2000cwa.googlegroups.com...
> unsested, something like
> where rID in(select rId from(select rId, count(*) from results group by
> rId having count(*)=3 ...
>

"Andrew Jocelyn" wrote:

> Hi
>
> I have a results set which is contains IDs where the word column matches
> some search criteria. Here is a simplified example. I can get the results as
> if it was an 'OR' search but I need to make it like an 'AND' search. Please
> see below.
>
> create table #results (rID int, word varchar(50))
>
> insert #results values (1, 'a')
> insert #results values (1, 'b')
> insert #results values (1, 'c')
> insert #results values (2, 'a')
> insert #results values (2, 'b')
> insert #results values (3, 'a')
> insert #results values (3, 'b')
> insert #results values (3, 'c')
> /*
>
> The table returns all IDs where the 'word' column contains either an a, b,
> c. What I want is a results set where the IDs are returned only if they
> contain all 3 words (a, b AND c). I.e. these results shouldn't contain rID 2
> because it doesn't have a 'c' match.
>
> */
> select * from #results
>
> drop table #results
>
> /*
> Results required:
> rID    word
> ------ -------
> 1      a
> 1      b
> 1      c
> 3      a
> 3      b
> 3      c
>
> */
>
> Many thanks
> Andrew
>
>
>