title: SQL how to select unique one-to-one pairings
body: <p><em>edited to make clearer - many apologies for the confusion of the original example</em></p>
  
  <p>I have the following table structure representing married couples:</p>
  
  <pre><code>id | Person | Spouse
  ______________________
  1  | Mary   | John
  2  | John   | Mary
  3  | Katy   | Bob
  4  | Bob    | Katy
  5  | Mary   | John
  6  | John   | Mary
  </code></pre>
  
  <p>In this example Mary is married to John, Katy to Bob and a <strong>different</strong> Mary is married to a different John.</p>
  
  <p>How can I retrieve these pairs of married couples?</p>
  
  <p>I have got close with this:</p>
  
  <pre><code>SELECT 
    p.id id1,
    q.id id2
  FROM 
    people p 
    INNER JOIN people q ON
      p.person = q.spouse AND 
      q.person = p.spouse AND 
      p.id &lt; q.id
  ORDER BY p.id
  </code></pre>
  
  <p>However this returns:</p>
  
  <pre><code>1 | 2 (1st Mary &amp; 1st John)
  1 | 6 (1st Mary &amp; 2nd John) *problem*
  2 | 5 (1st John &amp; 2nd Mary) *problem*
  3 | 4 (Katy &amp; Bob)
  5 | 6 (2nd Mary &amp; 2nd John)
  </code></pre>
  
  <p>How can I make sure the 1st Mary and 1st John are only married once (i.e. remove the problem rows above)?</p>
  
  <p>Many thanks</p>
  
  <p>Here's the SQL to create the example:</p>
  
  <pre><code>CREATE TABLE people
      (`id` int, `person` varchar(7), `spouse` varchar(7))
  ;
  
  INSERT INTO people
      (`id`, `person`, `spouse`)
  VALUES
      (1, 'Mary', 'John'),
      (2, 'John', 'Mary'),
      (3, 'Katy', 'Bob'),
      (4, 'Bob', 'Katy'),
      (5, 'Mary', 'John'),
      (6, 'John', 'Mary')
  ;
  
  SELECT 
    p.id id1,
    q.id id2
  FROM 
    people p 
    INNER JOIN people q ON
      p.person = q.spouse AND 
      q.person = p.spouse AND 
      p.id &lt; q.id
  ORDER BY p.id
  ;
  </code></pre>