Mysql – Identify Duplicates in MySQL across columns

MySQL

I have a table with name, contact1, contact2 and contact3 columns. Is there a way to find out of a given number exists in either of the 3 contact columns?

I am looking identify if a number exists more than once across contact1, contact2 and contact3 columns.

Please help.

Best Answer


You want a query like this one:

SELECT contact_id, contact1, contact2, contact3
FROM contact
WHERE 
(
  (contact1 = contact2)
  OR
  (contact2 = contact3)
  OR
  (contact1 = contact3)
);


See the db-fiddle here (see DDL and DML below).

Result:

contact_id  contact1    contact2    contact3
____________________________________________
         3   phone31     phone33     phone33

Of course, if MySQL had CHECK CONSTRAINTs, this problem could be avoided at the DDL level! From here, you have this gem:

CHECK

The CHECK clause is parsed but ignored by all storage engines.



============= DDL and DML ============

CREATE TABLE contact
(
  contact_id INTEGER,
  contact1 VARCHAR(20),
  contact2 VARCHAR(20),
  contact3 VARCHAR(20)
);

INSERT INTO contact
VALUES
(1, 'phone11', 'phone12', 'phone13'),
(2, 'phone21', 'phone22', 'phone23'),
(3, 'phone31', 'phone33', 'phone33'), -- << Duplicate number!
(4, 'phone41', 'phone42', 'phone43'),
(5, 'phone51', 'phone52', 'phone53');
Related Question