mysql - SQL - Select all primary keys for set of columns with duplicate values -

consider table as-

mysql> select * db;  +------+--------+------+------------+---------+ | udid | beneid | dept | scheme     | name    | +------+--------+------+------------+---------+ |    1 |     55 | tcs  | rc         | shelly  | |    2 |     95 | tcs  | rc         | bob     | |    3 |     75 | tcs  | rc         | ulrich  | |    4 |     55 | tcs  | rc         | shelly  | |    5 |     85 | tcs  | fs         | shelly  | |    6 |     65 | dssp | abc        | bob     | |    7 |     65 | dssp | abc        | bob     | |    8 |     75 | dssp | abc        | ulrich  | +------+--------+------+------------+---------+ 

there duplicate values set of columns beneid, dept , scheme, different primary key, udid. is,

mysql> select dept, scheme, beneid, count(*) cn db group 1, 2, 3 having cn >1; +------+--------+--------+----+ | dept | scheme | beneid | cn | +------+--------+--------+----+ | dssp | abc    |     65 |  2 | | tcs  | rc     |     55 |  2 | +------+--------+--------+----+ 

we have 2 duplicate columns dept, scheme, beneid different primary keys (udid). how can list out udids above result ?

expected table structure -

+------+--------+--------+----+-----+ | dept | scheme | beneid | cn | udid| +------+--------+--------+----+-----+ | dssp | abc    |     65 |  2 | 6   | | dssp | abc    |     65 |  2 | 7   | | tcs  | rc     |     55 |  2 | 1   | | tcs  | rc     |     55 |  2 | 4   | +------+--------+--------+----+-----+ 

you need "go back" original table, filtering table rows have found being duplicates.

example:

select db.dept, db.scheme, db.beneid, dups.cn, db.uid db inner join   (     select dept, scheme, beneid, count(*) cn     db group 1, 2, 3 having cn >1   ) dups on db.dept = dups.dept      , db.scheme = dups.scheme      , db.beneid = dups.beneid