Re: cartesian product

On Mon, 2007-02-19 at 10:58, Salman Tahir wrote:
> Hi,
> 
> I have a query regarding an SQL statement I'm trying to execute. I
> have the following table:
> 
> sequence
> -------------+
> AK
> AKCMK
> CMKA
> 
> I execute the following statement (Cartesian product):
> 
> SELECT p1.sequence as sequence1, p2.sequence as sequence2
> FROM potential_pairs p1, potential_pairs p2
> 
> which gives me:
> 
> sequence1 | sequence2
> ----------------+--------------
>  AK           | AK
>   AK           | AKCMK
>   AK           | CMKA
>   AKCMK    | AK
>   AKCMK    | AKCMK
>   AKCMK    | CMKA
>   CMKA      | AK
>   CMKA      | AKCMK
>   CMKA      | CMKA
> (9 rows)
> 
> I want to eliminate duplicates and by duplicate I mean a tuple such as
> {AK, CMKA} should be regarded as the same as {CMKA, AK}. So I would
> like the following result:
> 
> sequence1 | sequence2
> ----------------+--------------
>  AK           | AK
>   AK           | AKCMK
>   AK           | CMKA
>   AKCMK    | AKCMK
>   AKCMK    | CMKA
>   CMKA      | CMKA
> 


SELECT p1.sequence as sequence1, p2.sequence as sequence2 FROM
potential_pairs p1, join potential_pairs p2 on
(p1.sequence<>p2.sequence) where sequence1 > sequence2