MySQL query with equality and please all results...

Question

Hello,

I am quite new to MySQL and it is great in reducing data through queries. However, it seems to be hard to get both records, if you want records containing duplicates.

The table looks like:

ID  Value  more columns..
1     4
1     4
1     5
2     4
3     5
3     5
4     4
4     5

I want both(!) records with duplicate values, like

 ID Value  more columns..
 1    4
 1    4
 3    5
 3    5

I need them both... as there is information in the other columns why the values are equal.

Thanks a lot!

Answer 1

This query selects all records that have at least one other record with the same values of id and value:

SELECT  *
FROM    mytable mi
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    mytable mo
        WHERE   mo.id = mi.id
                AND mo.value = mi.value
        LIMIT 1, 1
        )

Answer 2

select *
  from mytable
 where (id, value)
    in (select * from (select id
             , value
          from mytable
        group
            by id
             , value
        having count(*) > 1))