mysql - Query to find duplicates in different fields -
i have searched , found refers duplicates of same field on record. database using stores computer hardware , among fields en0 , en1 contain ip addresses. need find records duplicate ips exist in either field. simplicity here 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 query return records 1 and/or 3. getting either duplicate ok, best return both.
thanks
you can use following query:
select distinct id, serial_no, en0, en1 mytable inner join (select ip ( select id, serial_no, en0 ip mytable union select id, serial_no, en1 ip mytable ) t group ip having count(*) > 1) t on t.ip = en0 or t.ip = en1
the above return all records containing duplicate ips.
the following subquery:
select ip ( select id, serial_no, en0 ip mytable union select id, serial_no, en1 ip mytable ) t group ip having count(*) > 1;
is used return duplicate ips. note use of union
instead union all
. way ip repeated in columns en0
, en1
of same record not considered duplicate. change union all
if want different behavior.
Comments
Post a Comment