sql stmt for: finding all groups containing 1 to n members

  • Thread starter Thread starter Joe Butler
  • Start date Start date
J

Joe Butler

using sql,

with this table:

id_group id_user
g1 u1
g1 u2
g1 u3
g2 u1
g2 u2
g2 u4
g3 u2
g3 u4
g3 u5

how can I get a list of id_group's that contain a sub-set of specified
users?

E.g.

specifying
(id_user = u2) and (id_user = u4)

would produce result set:
g2
g3

specifying
(id_user = u1) and (id_user = u2) and (id_user = u3)

would produce result set:
g1


I'm think I should be using one of these methods to do it...

SQL: IN, GROUP BY, HAVING
or
SQL: TRANSFORM, PIVOT

Being new to SQL and only done a bit of straightforward database stuff
before, this is not entirely obvious to me yet.

tia
 
JB> using sql,

JB> with this table:

JB> id_group id_user
JB> g1 u1
JB> g1 u2
JB> g1 u3
JB> g2 u1
JB> g2 u2
JB> g2 u4
JB> g3 u2
JB> g3 u4
JB> g3 u5

JB> how can I get a list of id_group's that contain a
JB> sub-set of specified users?


select distinct id_group from thistable t1
where exists (select 1 from thistable t2 where id_user='u2' and
id_group=t1.id_group)
and exists (select 1 from thistable t2 where id_user='u4' and
id_group=t1.id_group)
....


or


select id_group from thistable t1
inner join thistable t2
on t1.id_group=t2.id_group
where t1.id_user='u2' and t2.id_user='u4'


Vadim
 
I thought maybe you wanted to automate the querys suggested to you already.
I hope it's not too late, but here's what I came up with (in Transact-SQL):

create table #test (id_group varchar(20) NOT NULL, id_user varchar(20) NOT
NULL)
go

INSERT #test
SELECT id_Group = 'g1', id_user = 'u1' UNION
SELECT 'g1', 'u2' UNION
SELECT 'g1', 'u3' UNION
SELECT 'g2', 'u1' UNION
SELECT 'g2', 'u2' UNION
SELECT 'g2', 'u4' UNION
SELECT 'g3', 'u2' UNION
SELECT 'g3', 'u4' UNION
SELECT 'g3', 'u5'
go

declare @user1 varchar(20), @user2 varchar(20), @user3 varchar(20), @user4
varchar(20), @nbparam int
set @user1 = 'u1'
set @user2 = 'u2'
set @user3 = 'u3'
set @user4 = NULL

set @nbparam = case when @user1 is not null then 1 else 0 end + case when
@user2 is not null then 1 else 0 end
+ case when @user3 is not null then 1 else 0 end + case when
@user4 is not null then 1 else 0 end

select nbparam = @nbparam, user1 = @user1, user2 = @user2, user3 = @user3,
user4 = @user4

select id_group
from #test
where id_user in (@user1, @user2, @user3, @user4) /*It's OK to have NULL
values in the list of items*/
group by id_group
having count(*) = @nbparam
go
 
Back
Top