Find Duplicate rows & DISPLAY

Question

I need to find & display duplicate rows in my mysql table.
I found a solution to FIND duplicate records, from this article.
But Now I want to DISPLAY the found rows(Both records - Original record & Duplicates) like below.

    Orginal
ID   firstname  lastname ect..
100  John       Martin   blah

    Possible Duplicate/s
ID   firstname  lastname ect..
101  John       Martine   blah

Please help me with this.
I have know idea how to display/print both records.

SQL Query

    SELECT firstname, 
   lastname, 
   list.address 
FROM list
   INNER JOIN (SELECT address
               FROM   list
               GROUP  BY address
               HAVING COUNT(id) > 1) dup
           ON list.address = dup.address;

Edit : Dear All, I am clear with mysql query.
I want to print is to a specific page using PHP

score 1 · Answer 1 · answered Jun 13 '17 at 03:40

1

Please find this query for your purpose.

SELECT
    l.firstname, l.lastname, COUNT(*)
FROM
    list l
GROUP BY
    l.firstname, l.lastname
HAVING 
    COUNT(*) > 1

This will return duplicate records with occurrence in last column. You can alter having condition to match with your purpose. For your case, COUNT(*) > 0

answered Jun 13 '17 at 03:40

Virendra Jadeja

821
1
10
20

I am ok with sql query. But still i haven't any idea about how to print returned records. – Lanka Jun 13 '17 at 07:14
Can you tell me where you want to print records? – Virendra Jadeja Jun 13 '17 at 08:11
I want to print it on a page called duplicate-finder.php – Lanka Jun 13 '17 at 09:58

score 0 · Answer 2 · answered Jun 13 '17 at 03:44

0

Do something along this line:

Select a.*, b.* from list as a, list as b where a.address=b.address and a.id<>b.id

answered Jun 13 '17 at 03:44

Tim Morton

2,614
1
15
23

Find Duplicate rows & DISPLAY

2 Answers2