Finding duplicate records in table along with its related other duplicate

Question

I'm trying to find duplicate records in my table and I have the code to show the records but I need to modify it so that it also shows the other duplicate record. My example table is the following:

TABLE IMAGE

As you can see, the table (t_sen) contains duplicate records and I have this code to display the duplicate records:

    $sql = "SELECT ID, PARA_NUMBER, TEXT FROM t_sen GROUP BY TEXT having count(*) >= 2";

    $results = mysqli_query($conn, $sql) or die(mysqli_error());

    while($row = mysqli_fetch_assoc($results)){

        foreach($row as $column => $value){
            print "$column: $value <br>";
        }

        print "<br>";
    }

The output that I get is:

ID  |   PARA_NUMBER  |  TEXT
----------------------------
3   |   1            |  is
7   |   2            |  live

However the output that I want is:

ID  |   PARA_NUMBER  |  TEXT
----------------------------
3   |   1            |  is
4   |   1            |  is

7   |   2            |  live
8   |   2            |  live

I want it to show the other duplicate column also within the defined PARA_NUMBER. So an example (made up?) query would be something like:

SELECT ID, PARA_NUMBER, TEXT FROM t_sen (WITHIN PARA_NUMBER = 1) having count(*) >= 2

So the output should be:

ID  |   PARA_NUMBER  |  TEXT
----------------------------
3   |   1            |  is
4   |   1            |  is

But it would be much better if I don't have to repeat the query for every single PARA_NUMBER as there are many numbers and just one query can display all the duplicate records.

Hope I'm clear. Any help would be much appreciated.

Answer 1

This query will do what you want. It uses a subquery to find all the values of TEXT which have duplicates in the table, and JOINs that to the original table so that you can display each entry which has the duplicate TEXT value.

SELECT ID, PARA_NUMBER, t1.TEXT FROM
t_sen t1
JOIN (SELECT TEXT
      FROM t_sen
      GROUP BY TEXT
      HAVING COUNT(*) >= 2) t2
ON t1.TEXT = t2.TEXT

Output:

ID  PARA_NUMBER     TEXT
3   1               is
4   1               is
7   2               live
8   2               live

SQLFiddle Demo

If you only want to get the duplicates in PARA_NUMBER 1, for example, just add a WHERE clause to the end:

SELECT ID, PARA_NUMBER, t1.TEXT FROM
t_sen t1
JOIN (SELECT TEXT
      FROM t_sen
      GROUP BY TEXT
      HAVING COUNT(*) >= 2) t2
ON t1.TEXT = t2.TEXT
WHERE PARA_NUMBER = 1

Output:

ID  PARA_NUMBER     TEXT
3   1               is
4   1               is

Edit

Based on OPs requirement to be able to edit the rows in phpMyAdmin, a query with a JOIN will not solve the problem. Instead a query such as this one is required:

SELECT ID, PARA_NUMBER, t1.TEXT FROM
t_sen t1
WHERE EXISTS (SELECT *
              FROM t_sen t2
              WHERE t2.PARA_NUMBER = t1.PARA_NUMBER AND
                    t2.TEXT = t1.TEXT AND
                    t2.ID != t1.ID)

Output:

ID  PARA_NUMBER     TEXT
3   1               is
4   1               is
7   2               live
8   2               live

Answer 2

I always use grouping to find duplicates. My suggestion:

SELECT GROUP_CONCAT(CONCAT_WS('-', ID, PARA_NUMBER, TEXT)) AS data FROM t_sen GROUP BY PARA_NUMBER HAVING COUNT(*) > 2

This will give you results in this format:

| data          |
| 3-1-is,4-1-is |

Answer 3

SELECT t.ID, t.PARA_NUMBER, t.TEXT FROM
(SELECT TEXT FROM t_sen GROUP BY TEXT having count(*) >= 2) as duplicateValue
LEFT JOIN t_sen as t on duplicateValue.TEXT = t.TEXT
WHERE 1;

Answer 4

Left Join

SELECT
    t1.*
FROM
    t_sen AS t1
LEFT JOIN 
    t_sen AS t2 ON t1.ID != t2.ID AND t1.PARA_NUMBER = t2.PARA_NUMBER 
WHERE
    t2.ID IS NOT NULL 

Fiddle

• t1.ID != t2.ID excludes matching the same row in both tables (well both sides of the join)
• t1.PARA_NUMBER = t2.PARA_NUMBER joins the tales on the duplicate number
• WHERE t2.ID IS NOT NULL excludes records that are not duplicate.

you could also use TEXT which is a really bad name for a column, by adding that to the ON part of the join, in addition or in place of PARA_NUMBER as you needs require.

Just for posterity

CREATE TABLE t_sen (
    ID INT,
    PARA_NUMBER  INT,
    `TEXT` VARCHAR(20)
);

#non duplate
INSERT INTO t_sen  (ID, PARA_NUMBER, `TEXT`)VALUES(1,10,'the');
#duplicates
INSERT INTO t_sen  (ID, PARA_NUMBER, `TEXT`)VALUES(3,1,'is');
INSERT INTO t_sen  (ID, PARA_NUMBER, `TEXT`)VALUES(4,1,'is');
INSERT INTO t_sen  (ID, PARA_NUMBER, `TEXT`)VALUES(7,2,'live');
INSERT INTO t_sen  (ID, PARA_NUMBER, `TEXT`)VALUES(8,2,'live');

Answer 5

Below query should print all the records that have duplicate on PARA_NUMBER and TEXT field-

SELECT ID, PARA_NUMBER, TEXT FROM t_sen WHERE (PARA_NUMBER, TEXT) IN (SELECT PARA_NUMBER, TEXT FROM t_sen GROUP BY PARA_NUMBER, TEXT having count(*) >= 2)