title: SQL Server - find the person(s) that are each in all of the specified groups
body: <pre><code>declare @person_groups table (person_id int NOT NULL, group_id int NOT NULL)
  insert into @person_groups(person_id, group_id) values (1000, 501) --sample data
  insert into @person_groups(person_id, group_id) values (1001, 501) --sample data
  insert into @person_groups(person_id, group_id) values (1001, 502) --sample data
  insert into @person_groups(person_id, group_id) values (1001, 503) --sample data
  insert into @person_groups(person_id, group_id) values (1002, 502) --sample data
  insert into @person_groups(person_id, group_id) values (1002, 503) --sample data
  
  declare @tempGroupList table (group_id int)
  insert into @tempGroupList(group_id) values (501) --sample data
  insert into @tempGroupList(group_id) values (502) --sample data
  insert into @tempGroupList(group_id) values (503) --sample data
  </code></pre>
  
  <p>I need to find the person ids in table @person_groups who are each in every group listed in @tempGroupList</p>
  
  <p>pseudo code for a group list of 501, 502, 503,... :</p>
  
  <pre><code>SELECT person_id 
  FROM   @person_groups 
  WHERE  person_id is a member of group #501 
  and that same person is also a member of group #502
     and that same person is also a member of group #503) 
     and so on for each group id contained in @tempGroupList)
  </code></pre>
  
  <p>I am currently doing this with a cursor (yucky, I know). I am trying to refactor this on MS SQL Server (up to 2012) to not use a cursor (nor dynamic sql) but am having some trouble.. </p>
  
  <p>Can anyone shed some light on a good solution?</p>