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.

fiddle demo here

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.