Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: distinct subset query

Re: distinct subset query

From: Aloha Kakuikanu <aloha.kakuikanu_at_yahoo.com>
Date: 17 Jul 2006 13:56:23 -0700
Message-ID: <1153169783.169810.137890@p79g2000cwp.googlegroups.com>

-CELKO- wrote:
> This is a bit rough and untested, so forgive me
>
> CREATE TABLE Equiv_Classes
> (set_nbr INTEGER NOT NULL,
> element_name CHAR(2) NOTB NULL,
> PRIMARY KEY (set_nbr, element_name));
>
> The OUTER UNION is part of SQL-92, but nobody implements it. Pretend
> with me (you can fake it with UNION and EXCEPT operators
>
> SELECT S1.set_nbr, S2.set_nbr
> FROM Equiv_Classes AS S1, Equiv_Classes AS S2
> WHERE S1.set_nbr <= S2.set_nbr
> AND NOT EXISTS
> (SELECT set_nbr, element_name
> FROM Equiv_Classes AS S3
> WHERE S3.set_nbr = S1.set_nbr
> OUTER UNION
> SELECT set_nbr, element_name
> FROM Equiv_Classes AS S4
> WHERE S4.set_nbr = S2.set_nbr
> ON S4.set_nbr <> S3.set_nbr);
>
> The NOT EXISTS() says that the two different sets are equal because
> they have no elements outside either of them. So if sets 1,2,3,4 were
> identical, I would get (1,1), (2,2) (3,3), (4,4), (1,2), (1,3),
> (1,4), (2,3), (2,4) in my output.
>
> While that is right, I woufl probably want to represent the equivalent
> classes as { (1,2), (1,3), (1,4)} with set_nbr = 1 as defining value
> for the class.
>
> There ought to be some clever way to use EXCEPT, UNION and INTERSECT
> to do this, but I need my sleep.

This would be symmetric difference, not outer union: SELECT distinct S1.set# s1, S2.set# s2
  FROM Equiv_Classes S1, Equiv_Classes S2 WHERE NOT EXISTS
    (SELECT element

         FROM Equiv_Classes  S3
        WHERE S3.set# = S1.set#

    UNION
    SELECT element
         FROM Equiv_Classes  S4
        WHERE S4.set# = S2.set#

    MINUS
    (SELECT element
         FROM Equiv_Classes  S3
        WHERE S3.set# = S1.set#

    INTERSECT
    SELECT element
         FROM Equiv_Classes  S4
        WHERE S4.set# = S2.set#)

)
; Received on Mon Jul 17 2006 - 15:56:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US