Please help find duplicate entries in multiple columns of same row in MySQL:
-
4Poor table design. Why several columns with same type of data?jarlh– jarlh2017-12-01 08:35:22 +00:00Commented Dec 1, 2017 at 8:35
-
Please show us your code and what you have tried so far.kscherrer– kscherrer2017-12-01 08:35:59 +00:00Commented Dec 1, 2017 at 8:35
-
What exactly are you trying to achieve?Soolie– Soolie2017-12-01 08:37:07 +00:00Commented Dec 1, 2017 at 8:37
-
Yes. Fix your schema designStrawberry– Strawberry2017-12-01 08:37:14 +00:00Commented Dec 1, 2017 at 8: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 workkscherrer– kscherrer2017-12-01 08:38:29 +00:00Commented Dec 1, 2017 at 8:38
|
Show 4 more comments
3 Answers
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
Comments
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
}
}
2 Comments
Prakash Lal
but what if i have 100 columns in one row? It will too much write then!
Jaydeep Mor
So write this first in your question.
SELECT Sample1, COUNT(*) C FROM tablename GROUP BY Sample1 HAVING C > 1;
2 Comments
kscherrer
what if sample1 = 'Sam', sample2 = 'Rob', sample3 = 'Rob'?
Chidambaram
Then add sample1, sample2 and sample3