Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Question Unanswered: mysql query for within same table

    Hello,

    Please advise on the best way to achieve the following task:

    I have a table with format something like this:

    table name main:

    ID1 ID2 info
    1 2 x
    1 3 y
    1 1 z
    2 2 a
    2 3 b
    1 2 c


    I wish to query for unique entries meaning ones that have ID1 and ID2s repeated are filtered out. For example the 1st and last entry above will be filtered out to be just 1 entry even though they have different info.

    I tried to do :
    select a.* from main a, main b where a.ID1 != b.ID1 and a.ID2 != b.ID2;

    But this seems to not work since it is returning all entries. I know this works:
    select a.* from main a, main b where a.ID1 = b.ID1 and a.ID2 = b.ID2;

    it is selecting the ones that do repeat, how could I do the opposite? By "subtracting" the ones that do repeat? Keep in mind that I need to keep at least one entry even if it is repeated 10 times.

    thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: mysql query for within same table

    Originally posted by dzhang1978
    Hello,

    Please advise on the best way to achieve the following task:

    I have a table with format something like this:

    table name main:

    ID1 ID2 info
    1 2 x
    1 3 y
    1 1 z
    2 2 a
    2 3 b
    1 2 c


    I wish to query for unique entries meaning ones that have ID1 and ID2s repeated are filtered out. For example the 1st and last entry above will be filtered out to be just 1 entry even though they have different info.
    SELECT COUNT(*) AS occ, id1, id2, info
    FROM test12
    GROUP BY id1, id2
    HAVING occ = 1;

    Hope this helps.

  3. #3
    Join Date
    Mar 2004
    Posts
    3

    Question Re: mysql query for within same table

    Originally posted by bstjean
    SELECT COUNT(*) AS occ, id1, id2, info
    FROM test12
    GROUP BY id1, id2
    HAVING occ = 1;

    Hope this helps.

    Hello,

    Thanks much for your help. This works but solves half the problem. It picks the unique entries but leaves all ones that repeat. Remember, I have to keep at least 1 entry from the repeated. i.e. in my list, I would keep the first entry and throw away the last one since they share same ID1/ID2.

    Would I have to do write 2 queries and join them? How to do the 2nd query? thanks

  4. #4
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: mysql query for within same table

    If you leave off the HAVING clause, you won't lose the repeated records. Including info in the records that you select may not mean a whole lot. MySQL will just choose one of the records in the repeated records to take info from. Most RDBMS systems do not allow you to do this, but MySQL extended it and provides a warning in the documentation about it.
    http://www.mysql.com/doc/en/GROUP-BY-hidden-fields.html

Posting Permissions

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