Query to find duplicates in different fields

Question

I have searched and everything I found refers to duplicates of the same field on a record. The database I am using stores computer hardware and among the fields are en0 and en1 which contain ip addresses. I need to find records with duplicate ips that exist in either field. For simplicity here is a sample table:

id  serial_no           en0           en1
 1    0000001  10.200.5.102  10.200.5.103
 2    0000002  10.200.6.102  10.200.6.103
 3    0000003  10.200.5.110  10.200.5.102

I need the query to return records 1 and/or 3. Getting either duplicate is ok, best would be to return both.

Thanks

"en0 and en1" - because, why normalize? Anyway you could use a self-join. — CodeCaster
– CodeCaster, Commented Jun 10, 2015 at 13:50
Tank you very much for your snarky remark. The table is legacy and normalization is not an option. I will work on the self-join. — wesmat
– wesmat, Commented Jun 10, 2015 at 14:14
"normalization is not an option" - you could do so in a view. — CodeCaster
– CodeCaster, Commented Jun 10, 2015 at 14:16

Giorgos Betsos · Accepted Answer · 2015-06-10 15:29:11Z

1

You can use the following query:

SELECT DISTINCT id, serial_no, en0, en1
FROM mytable 
INNER JOIN (SELECT ip
            FROM (
               SELECT id, serial_no, en0 AS ip
               FROM mytable

               UNION 

               SELECT id, serial_no, en1 AS ip
               FROM mytable ) t
            GROUP BY ip
            HAVING COUNT(*) > 1) t
ON t.ip = en0 OR t.ip = en1

The above will return all records containing duplicate IPs.

Fiddle Demo here

The following subquery:

SELECT ip
FROM (
  SELECT id, serial_no, en0 AS ip
  FROM mytable

  UNION 

  SELECT id, serial_no, en1 AS ip
  FROM mytable ) t
GROUP BY ip
HAVING COUNT(*) > 1;

is used to return duplicate IPs. Note the use of UNION instead UNION ALL. This way an IP that is repeated in columns en0, en1 of the same record will not be considered as duplicate. Change to UNION ALL if you want different behavior.

edited Jun 10, 2015 at 15:29

answered Jun 10, 2015 at 14:05

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

7 Comments

wesmat Over a year ago

Thanks for this fantastic answer and for including the sql fiddle - I did not know it existed. I have used various code fiddles before but not for sql! I am getting an "Error Code: 1242. Subquery returns more than 1 row" when I run on my server, which is 5.6. I did convert the table and field names to the actuals for the real DB and perhaps I messed that up. I am re-checking and also creating the sample table I used here. Hopefully I will find that problem.

wesmat Over a year ago

So duplicating the fiddle example in the DB worked fine - I'll work out how to apply to the real DB. Thanks again for your help.

Giorgos Betsos Over a year ago

@wesmat There is a problem with my query. The WHERE clause should normally be like en0 IN (... subquery here ) OR en1 IN (...subquery repeated here)`. I tried to do it the other way round in order to make it more succinct, but it doesn't work if the subquery return more than one value.

Giorgos Betsos Over a year ago

@wesmat Check this for an alternative solution using JOIN.

wesmat Over a year ago

So I found the problem and the fiddle also has it. If I add another row that is a full duplicate it fails. I added '0000004', '10.200.5.102', '10.200.5.103' to the fiddle and get an error.

|

Collectives™ on Stack Overflow

Query to find duplicates in different fields

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related