title: Select duplicate persons with duplicate memberships
body: <p><a href="http://sqlfiddle.com/#!18/93065/2" rel="nofollow noreferrer">SQL Fiddle</a> with schema and my intial attempt.</p>
  
  <pre><code>CREATE TABLE person
      ([firstname] varchar(10), [surname] varchar(10), [dob] date, [personid] int);
  
  INSERT INTO person
      ([firstname], [surname], [dob] ,[personid])
  VALUES
      ('Alice', 'AA', '1/1/1990', 1),
      ('Alice', 'AA', '1/1/1990', 2),
      ('Bob'  , 'BB', '1/1/1990', 3),
      ('Carol', 'CC', '1/1/1990', 4),
      ('Alice', 'AA', '1/1/1990', 5),
      ('Kate' , 'KK', '1/1/1990', 6),
      ('Kate' , 'KK', '1/1/1990', 7)
  
  ;
  
  CREATE TABLE person_membership
      ([personid] int, [personstatus] varchar(1), [memberid] int);
  
  INSERT INTO person_membership
      ([personid], [personstatus], [memberid])
  VALUES
      (1, 'A', 10),
      (2, 'A', 20),
      (3, 'A', 30),
      (3, 'A', 40),
      (4, 'A', 50),
      (4, 'A', 60),
      (5, 'T', 70),
      (6, 'A', 80),
      (7, 'A', 90);
  
  CREATE TABLE membership
      ([membershipid] int, [memstatus] varchar(1));
  
  INSERT INTO membership
      ([membershipid], [memstatus])
  VALUES
      (10, 'A'),
      (20, 'A'),
      (30, 'A'),
      (40, 'A'),
      (50, 'T'),
      (60, 'A'),
      (70, 'A'),
      (80, 'A'),
      (90, 'T');
  </code></pre>
  
  <p>There are three tables (as per the fiddle above).  <code>Person</code> table contains duplicates, same people entered more than once, for the purpose of this exercise we assume that a combination of the first name, surname and DoB is enough to uniquely identify a person.</p>
  
  <p>I am trying to build a query which will show duplicates of people (first name+surname+Dob) with two or more active entries in the <code>Person</code> table (<code>person_membership.person_status=A</code>) AND two or more active memberships (<code>membership.mestatus=A</code>).</p>
  
  <p>Using the example from SQL Fiddle, the result of the query should be just Alice (two active person IDs, two active membership IDs).
  <a href="https://i.stack.imgur.com/e7jxo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/e7jxo.png" alt="enter image description here"></a></p>
  
  <p>I think I'm making progress with the following effort but it looks rather cumbersome and I need to remove Katie from the final result - she doesn't have a duplicate membership.</p>
  
  <pre><code>SELECT q.firstname, q.surname, q.dob, p1.personid, m.membershipid 
  FROM
  (SELECT
    p.firstname,p.surname,p.dob, count(*) as cnt
  FROM
    person p
  GROUP BY
    p.firstname,p.surname,p.dob
  HAVING COUNT(1) &gt; 1) as q
  INNER JOIN person p1 ON q.firstname=p1.firstname AND q.surname=p1.surname AND q.dob=p1.dob
  INNER JOIN person_membership pm ON p1.personid=pm.personid
  INNER JOIN membership m ON pm.memberid = m.membershipid
  WHERE pm.personstatus = 'A' AND m.memstatus = 'A'
  </code></pre>