Re: [SQL] self-join and DISTINCT quandry.

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