select only the datasets that appear more than once in a table

Hi,

I'm trying to select only the datasets that appear more than once in a table.

Is there an elegant way using MySQL?

Thanks a lot.

Eg.:

table
1 john
2 mike
3 peter
4 john
5 heinz

result shall be
1 john
4 john

use the tabel twice (JOIN)

SELECT
 t1.id, t1.name
FROM
  tablename t1
  INNER JOIN tablename t2 ON 
    t1.name = t2.name
    AND t1.id != t2.id

hth

hach,

thanks for the fast help.
it works.

i tested it in the table with the planned 15000 rows and the database connection broke up after 300 sec.
is there a way to fasten the select?

Thanks a lot.

You indexed the fieldds?

type

EXPLAIN SELECT
 t1.id, t1.name
FROM
  tablename t1
  INNER JOIN tablename t2 ON 
    t1.name = t2.name
    AND t1.id != t2.id

in phpmyadmin and post the result

another possibility would be to count the occurecnes of the names and in a 2nd select retrieve the ids

thank you,
and sorry, i forgot the index. it works fine with 2 indizes over the 2 fields.