title: SQL: self join using each rows only once
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/7112513/combinations-not-permutations-from-cross-join-in-sql">combinations (not permutations) from cross join in sql</a>  </p>
  </blockquote>
  
  
  
  <p>I've currently got a table with the following records:</p>
  
  <pre><code>A1
  A2
  A3
  B1
  B2
  C1
  C2
  </code></pre>
  
  <p>Where the same letter denotes some criteria in common (e.g. a common value for the column 'letter').  I do a self join on the criteria as follows:</p>
  
  <pre><code>SELECT mytable.*, self.* FROM mytable INNER JOIN mytable AS self 
     ON (mytable.letter = self.letter and mytable.number != self.number);
  </code></pre>
  
  <p>This join gives something like the following:</p>
  
  <pre><code>A1 A2
  A2 A1
  A1 A3
  A3 A1
  A2 A3
  A3 A2
  B1 B2
  B2 B1
  C1 C2
  C2 C1
  </code></pre>
  
  <p>However, I only want to include each pair once (a combination instead of a permutation).
  How would I get the following:</p>
  
  <pre><code>A1 A2
  A1 A3
  A2 A3
  B1 B2
  C1 C2
  </code></pre>