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

Stuart Rison <rison(at)biochemistry(dot)ucl(dot)ac(dot)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