Stuart Rison <rison@biochemistry.ucl.ac.uk> writes:
> But what is I didn't want the information to appear twice. I could try
> adding DISTINCT but that would not work because row a,1,7 is of course
> DISTINCT from a,7,1 although I am looking for such duplication to be
> eliminated...
Bit of a hack, but you could add the condition... AND t1.number < t2.number;
to get rid of the two-way duplication that otherwise arises from
your join. (Any other way of selecting just one of the pair
of matches would work as well, of course; you could compare the
two row's OIDs if nothing else was handy.)
Not sure if you still need DISTINCT after that.
regards, tom lane