solved by Kent on MSDN
!declare @test table
( recId integer,
GroupId integer,
[User] varchar(9))
insert into @test
select 1 , 1, 'John' union all
select 2 , 1, 'Mary' union all
select 3 , 1, 'Fred' union all
select 4 , 2, 'Joe ' union all
select 5 , 2, 'Mary' union all
select 6 , 3, 'Fred' union all
select 7 , 3, 'John ' union all
select 8 , 3, 'Mary' union all
select 9 , 4, 'Tony' union all
select 10, 5, 'Fred'
select
GroupId
from @test
where [user] in ('John','Mary','Fred')
group by groupId
having count(distinct [user]) = 3
/* -------- Output: --------
GroupId
-----------
1
3
(1 row(s) affected)
*/
select count(*) as Group_Count
from
( select
GroupId
from @test
where [user] in ('John','Mary','Fred')
group by groupId
having count(distinct [user]) = 3
) x
/* -------- Output: --------
Group_Count
-----------
2
*/