select problem

[Stephan Hoppe]

Hi guys, I am trying to figure out how to solve this simple select statement, but somehow I am unable to get it all together. I simplified my problem which is like this. I have a unlimited number of persons and teams. Each person can be a member of one or more teams. I would like to figure out which person is a member of all teams. To make it easier for you to follow here is a SQL snippet to create sample tables and data

create table #person
(
PersonId varchar(2),
Firstname nvarchar(50)
)

create table #member
(
PersonId varchar(2),
TeamId varchar(2)
)

create table #team
(
TeamId varchar(2),
TeamName nvarchar(50)
)

insert into #person values ('P1', 'Wayne');
insert into #person values ('P2', 'Rick');
insert into #person values ('P3', 'Bob');

insert into #team values ('T1', 'Team 1');
insert into #team values ('T2', 'Team 2');
insert into #team values ('T3', 'Team 3');

insert into #member values ('P1', 'T1');
insert into #member values ('P2', 'T1');
insert into #member values ('P2', 'T2');
insert into #member values ('P2', 'T3');
insert into #member values ('P3', 'T3');

drop table #member
drop table #person
drop table #team

The only person in all teams is Rick. Thanks for any kind of help on this matter. Cheers! Byteloser P.S: By the way I am using SQL Server 2005

[leoinfo]

SELECT PersonId, Firstname FROM #person WHERE PersonId IN ( SELECT PersonId FROM #member GROUP BY PersonId HAVING COUNT(*) = (SELECT COUNT(*) FROM #team) )

[Stephan Hoppe]

Hi, Thanks for the reply. I came up with the same idea last night. Hope this works in my real work szenario, because I have a high data volume. Cheers! Stephan