SQL: self join, using each row only once.

Question

Possible duplicate:
combinations (not permutations) from cross join in sql

I currently have a table with the following entries:

A1 A2 A3 B1 B2 C1 C2 

If the same letter denotes some common criteria (for example, the general meaning for the letter "column"). I myself join the criteria as follows:

 SELECT mytable.*, self.* FROM mytable INNER JOIN mytable AS self ON (mytable.letter = self.letter and mytable.number != self.number); 

This compound gives approximately the following:

 A1 A2 A2 A1 A1 A3 A3 A1 A2 A3 A3 A2 B1 B2 B2 B1 C1 C2 C2 C1 

However, I only want to turn on each pair once (combination instead of permutation). How to get the following:

 A1 A2 A1 A3 A2 A3 B1 B2 C1 C2 

Answer 1

Changing the state of a JOIN will do a little what you want.

Instead:

 ON (mytable.letter = self.letter and mytable.number != self.number) 

using

 ON (mytable.letter = self.letter and mytable.number > self.number) 

This will only include combinations, where self.number greater than mytable.number , which actually limits the results to one correct order of each combination ...