-1

Please help find duplicate entries in multiple columns of same row in MySQL:

table description

Soolie
  • 1,812
  • 9
  • 21
Prakash Lal
  • 79
  • 1
  • 1
  • 9
  • 4
    Poor table design. Why several columns with same type of data? – jarlh Dec 01 '17 at 08:35
  • Please show us your code and what you have tried so far. – kscherrer Dec 01 '17 at 08:35
  • What exactly are you trying to achieve? – Soolie Dec 01 '17 at 08:37
  • Yes. Fix your schema design – Strawberry Dec 01 '17 at 08:37
  • It is not necessarily a poor table design. I can think of several good table design where this can happen and there can be the need to find out exactly what OP asks. Its just that the question as it is now is now at all complete with code, his attempts and an explanation why his solution doesn't work – kscherrer Dec 01 '17 at 08:38
  • Right @Cashbee ! – Jaydeep Mor Dec 01 '17 at 08:40
  • Not good design, but sometimes one cannot fix that. What defines the set of columns to be checked for duplicates? – Richard Dec 01 '17 at 08:43
  • do you want to find entries that have duplicate columns, or do you want to find out which columns have the same value as another column? – kscherrer Dec 01 '17 at 08:44
  • Apart from clearifying your requirements as already stated above, you can split the table into three with id and sample data, UNIONize them into one, select those which have duplicate id's and this you can use to bring beack the record. – Kurt Ludikovsky Dec 01 '17 at 09:22

3 Answers3

1

If you want to find the records that have duplicates in columns you can use this query:

SELECT T1.* FROM tbl T1
JOIN  
    (SELECT id
    FROM (
        SELECT id, sample1 AS n from tbl
        UNION ALL
        SELECT id, sample2 AS n from tbl
        UNION ALL
        SELECT id, sample3 AS n from tbl
    ) AS X
    GROUP BY id, n
    HAVING COUNT(*) > 1
) T2
    ON T1.id = T2.id;

You can also test it Here

Valerica
  • 1,618
  • 1
  • 13
  • 20
-1

I am not sure with MySql,

But in PHP below example will use.

Example

$query = "select * from table_name";
$result = mysqli_query($query);
while ($row = mysqli_fetch_array($result)) {
    if ($row['sample1'] == $row['sample2']) {
        // This row duplicate
    }
    else {
        // This row not duplicate
    }
}
Jaydeep Mor
  • 1,690
  • 3
  • 21
  • 39
-2
SELECT Sample1, COUNT(*) C FROM tablename GROUP BY Sample1 HAVING C > 1;

Finding duplicate values in MySQL

MySQL select records for duplicates using multiple columns

Chidambaram
  • 434
  • 4
  • 14