Finding possible duplicates in database

I have a database storing multiple columns and I am trying to create efficient sql query that would output all the possible duplicates based on selected columns (1 or more).

so for example if i would have database which has "Name" "Surname" "Phone" and I would like to be able to find duplicates by name and surname.开发者_如何学JAVA I want to ouput only the ones that are in database more then twice so the correct output would be something like this:

John, Smith, 123456789
John, Smith, 987654321
John, Smith, 098546786
Peter, Donut, 234569087
Peter, Donut, 854567896

I was searching for something but the only similar thing I have found out is something like

SELECT * FROM table GROUP BY name,surname HAVING COUNT(*)>1

which outputs only one occurance of the duplicate (not all of them). Any suggestions so that I dont have to select all the items from the database and do it via PHP?

If phone can be the same between duplicates, just use your primary key in its place on the join.

SELECT DISTINCT t.name, t.surname, t.phone FROM table t 
LEFT OUTER JOIN table t2 ON t.name = t2.name AND t.surname = t2.surname AND t.phone <> t2.phone
WHERE t2.name IS NOT NULL

SELECT ID, Name, LastName, Phone

FROM mytable T1, mytable T2

      WHERE

        T1.name = T2.name AND     

        T1.Phone = Phone AND

        T1.LastName = T2.PLastName AND

        T1.ID < T1.ID -- only use this line to leave 1 as unique

Would something like this work?

SELECT *
FROM table t1
INNER JOIN
(
    SELECT name, surname
    FROM table
    GROUP BY name,surname
    HAVING COUNT(*)>1) t2
ON t1.name = t2.name AND t1.surname = t2.surname