0

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.

1
  • Don't splay an array (of "contacts") across columns. Instead, have a separate table.
    – Rick James
    Nov 15, 2017 at 0:18

1 Answer 1

0


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');
3
  • The Query worked like a breeze. I used the following: codeSELECT count(*) FROM jmjdata WHERE ( (con1 = '8108600007') OR (con2 = '8108600007') OR (con3 = '8108600007') );code So when a count is over 1 it means there is a duplicate in either of those columns. Super! Also, on a DDL level how would I restrict them from adding a number that already exists? Or is that a PHP/Ajax question?
    – Lenrox
    Nov 14, 2017 at 16:35
  • MariaDB 10.2 on the other hand does have CHECK constraints.
    – dbdemon
    Nov 14, 2017 at 16:35
  • Incredibly, with MySQL, you're out of luck, even on into version 8!! You could try a real RDBMS :-) - PostgreSQL or, as suggested above, use MariaDB! On the other hand, you could enforce the constraint with a trigger - maybe your best route?
    – Vérace
    Nov 14, 2017 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.