Re: distinct subset query

From: -CELKO- <jcelko212_at_earthlink.net>
Date: 15 Jul 2006 21:14:40 -0700
Message-ID: <1153023280.151674.125070_at_m73g2000cwd.googlegroups.com>

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. Received on Sun Jul 16 2006 - 06:14:40 CEST