Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    1

    Unanswered: get equivalent whole by comparing its parts from a single table

    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,

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •