get equivalent whole by comparing its parts from a single table

kpinfix · #1 (**permalink**) 09-17-09, 02:02

I have a table T1 with 2 columns

C1 | C2
---------------
W1 p1
W1 p2
W2 P1
W2 p2
W2 p3
w3 p1
W3 p2

I have a set of records: Select * from T1 wehre C1 = W1
What I want is records that is equivalent to W1 in terms of of its parts list. Ie any results from Column C1 should have same parts as w1.

result should be W1 = W3 but not W2

how Can I acheive this?

------
The above problem can be More complex if there are aliases for parts in Column C2. For example a light change in the part list for W3. Where P4 is put instead of P2. P4 is alias of P2 ie P4=P2.

C1 | C2
---------------
W1 p1
W1 p2
W2 P1
W2 p2
W2 p3
w3 p1
W3 p4

Where P4 = P2 and all such alias information is kept in another mapping table MT. Expected result in this case is identical to first case is W1 and W3 should be in result set.

I could think of doing this easily if MySQL had intersection but it does not support and I dont know how I could achieve it otherwise. Any help from the community will be highly appriciated.

Thank you,

r937 · #2 (**permalink**) 09-17-09, 07:57

caution: untested

Code:

SELECT that.c1
  FROM ( SELECT this.c1 AS this_c1
              , that.c1 AS that_c1
              , COUNT(*) AS matches
           FROM t1 AS this
         INNER
           JOIN t1 AS that
             ON that.c2 = this.c2
            AND that.c1 <> this.c1
          WHERE this.c1 = 'p1'
         GROUP
             BY this.c1 
              , that.c1 ) AS maybe
INNER
  JOIN ( SELECT c1, COUNT(*) AS parts
           FROM t1
         GROUP
             BY c1 ) AS this_total
    ON this_total.c1 = maybe.this_c1
INNER
  JOIN ( SELECT c1, COUNT(*) AS parts
           FROM t1
         GROUP
             BY c1 ) AS that_total
    ON that_total.c1 = maybe.that_c1
 WHERE this_total.parts = maybe.matches
   AND this_total.parts = that_total.parts

Internet Services

Web Development

Digital Marketing

Consumer Tech