MySQL find duplicates in multiple columns

Question

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!

why does the manual entry not go into the same column as the barcode entry? — MuppetGrinder
– MuppetGrinder, Commented Apr 22, 2015 at 16:00
Is there a good reason for not capturing the ID in the same column regardless of automatic or manual entry? If it's that important to store how the ID was entered, you could have another field storing whether it was automatic or manual entry (if there are just these two options it could be a BOOLEAN). This seems like terrible design. — Eilidh
– Eilidh, Commented Apr 22, 2015 at 17:32

Ari Djemana · Accepted Answer · 2015-11-21 06:59:09Z

1

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

edited Nov 21, 2015 at 6:59

answered Apr 22, 2015 at 16:15

Ari Djemana

1,25912 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marcus Adams Over a year ago

You should rewrite this as a LEFT JOIN.

Joe Taras · Accepted Answer · 2015-04-22 16:02:57Z

0

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

answered Apr 22, 2015 at 16:02

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

Comments

Tom Mac · Accepted Answer · 2015-04-22 16:06:19Z

0

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;

answered Apr 22, 2015 at 16:06

Tom Mac

9,8633 gold badges32 silver badges40 bronze badges

Comments

Naami · Accepted Answer · 2015-04-22 16:31:03Z

0

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;

answered Apr 22, 2015 at 16:31

Naami

3583 silver badges9 bronze badges

Comments

Marcus Adams · Accepted Answer · 2015-04-22 16:49:41Z

0

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.

answered Apr 22, 2015 at 16:49

Marcus Adams

54k9 gold badges98 silver badges146 bronze badges

Collectives™ on Stack Overflow

MySQL find duplicates in multiple columns

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related