CODEWITHSUNDEEP
mysql
user3780463
user3780463

Reputation: 65

MySQL find duplicates in multiple columns

I have a table with user IDs split into 2 columns. (To explain this a little more, we capture the IDs of participants by scanning barcodes. Sometimes the barcode scanner function doesn't work for whatever reason, so we also allow manual entry of the ID, IF the barcode scanner doesn't work.) This results in data like the following:

+------+-----------+
|  ID  | ID_MANUAL |
+------+-----------+
| A    | NULL      |  
| NULL | A         |  
| B    | NULL      |  
| B    | NULL      |  
| NULL | C         |  
| C    | NULL      |  
| NULL | D         |  
| NULL | D         |  
+------+-----------+

I want to find all of the duplicate IDs, taking both columns into account. It's easy to find the duplicates that are only in 1 column ("B" and "D"). But how do I find the duplicates "A" and "C"? Ideally, the query would find and return ALL duplicates (A,B,C, and D).

Thanks!

Upvotes: 0

Views: 670

Answers (5)

Ari Djemana
Ari Djemana

Reputation: 1249

Try this:

SELECT DUP.* FROM (SELECT ID FROM yourtable) ORI
LEFT JOIN yourtable DUP ON DUP.ID = ORI.ID_MANUAL WHERE DUP.ID IS NOT NULL

Upvotes: 1

Marcus Adams
Marcus Adams

Reputation: 53830

You can do this with a simple JOIN, using COALESCE and DISTINCT if you have a surrogate auto-increment primary key:

SELECT DISTINCT s2.pk, s2.ID, s2.ID_MANUAL
FROM scans s1
JOIN scans s2
ON COALESCE(s2.ID, s2.ID_MANUAL) = COALESCE(s1.ID, s1.ID_MANUAL)
AND s2.pk > s1.pk

This will exclude the original record, so you could delete the records returned in this result set.

Here's the SQL Fiddle.

Upvotes: 0

Naami
Naami

Reputation: 358

try:

select id, count(*)
from
(
 select id
 from data
 where id_manual is null
 union all
 select id_manual as id
 from data
 where id is null
) a
group by id
having count(*) > 1;

and 

select id, id_manual
from data
group by id, id_manual
having count(*) > 1;

Upvotes: 0

Tom Mac
Tom Mac

Reputation: 9853

You could try UNION ALL here:

select id,count(*)
from
(
 select id
 from yourtable
 union all
 select id_manual as id
 from yourtable
) a
group by id
having count(*) >1;

Upvotes: 0

Joe Taras
Joe Taras

Reputation: 15379

An advice: a field named ID m,ust be unique and not null. But if you have this structure, you can try this:

SELECT id
FROM yourtable t
WHERE id is not null
AND
    (SELECT COUNT(*)
    FROM yourtable t2
    WHERE t2.id = t.id) +
    (SELECT COUNT(*)
    FROM yourtable t3
    WHERE t3.id_manual = t.id) > 1

UNION

SELECT id_manual
FROM yourtable t
WHERE id_manual is not null
AND
    (SELECT COUNT(*)
    FROM yourtable t2
    WHERE t2.id = t.id_manual) +
    (SELECT COUNT(*)
    FROM yourtable t3
    WHERE t3.id_manual = t.id_manual) > 1

You can go on Sql Fiddle

Upvotes: 0

Related Questions