MySQL find value repeating in multiple columns

Question

I am working on code that would check for any telephone number that appears more than once in table of employees. In this scenario different persons may have the same telephone number and I wish to flag them.




First name
Last Name
Main Phone
Work Phone
Mobile 1
Mobile 2


Jon
Smith
77777777
50505050
 
 


J 
Smith
 
 
 
 


John 
Smith
50505050
 
 
 


J Smith
 
 
77777777
 
50505050


Jane
Smith
 
 
 
77777777


J
Doe
65656565
 
 
33333333


Jessica
Doe
 
33333333
65656565

I have attempted to search for any number occurring twice using the below code without success and seek your advice.

    Select firstname, lastname, mainPhone, count(mainPhone), workPhone,count(businessPhone), mobile1Phone, count(mobilePhone)  , mobile2Phone, count(mobile2Phone)  
from employeeTable
group by mainPhone, businessPhone, mobile1Phone, mobile2Phone
having 
(count(mainPhone) > 1) or (count(businessPhone) > 1) or (count(mobile2Phone) > 1) or (count(mobile2Phone) > 1);

Daniel Koller · Accepted Answer

You could try something like that.

SELECT
  phone,
  GROUP_CONCAT(`uniqueKey`) AS `uniqueKeys`,
  COUNT(*)
FROM
  (
  SELECT
    id AS `uniqueKey`,
    'mainPhone' AS source,
    mainPhone AS `phone`
  FROM
    employeeTable

  UNION

  SELECT
    id AS `uniqueKey`,
    'businessPhone' AS source,
    businessPhone AS `phone`
  FROM
    employeeTable

  UNION

  SELECT
    id AS `uniqueKey`,
    'mobile1Phone' AS source,
    mobile1Phone AS `phone`
  FROM
    employeeTable

  UNION

  SELECT
    id AS `uniqueKey`,
    'mobile2Phone' AS source,
    mobile2Phone AS `phone`
  FROM
    employeeTable
  ) AS subquery1
GROUP BY phone

At first with the different statements connected by UNION you create something like a transpondet view. This can be grouped and counted. I assumed that there is some kind of unique or primary key like an id or login or similar.

MySQL find value repeating in multiple columns

Answers (1)

Related Questions