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.

<table style="height: 234px;" width="735">
<tbody>
<tr>
<td style="width: 114px;"><span style="text-decoration: underline;"><strong>First name</strong></span></td>
<td style="width: 114px;"><span style="text-decoration: underline;"><strong>Last Name</strong></span></td>
<td style="width: 116px;"><span style="text-decoration: underline;"><strong>Main Phone</strong></span></td>
<td style="width: 117px;"><span style="text-decoration: underline;"><strong>Work Phone</strong></span></td>
<td style="width: 117px;"><span style="text-decoration: underline;"><strong>Mobile 1</strong></span></td>
<td style="width: 117px;"><span style="text-decoration: underline;"><strong>Mobile 2</strong></span></td>
</tr>
<tr>
<td style="width: 114px;">Jon</td>
<td style="width: 114px;">Smith</td>
<td style="width: 116px;">77777777</td>
<td style="width: 117px;">50505050</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
</tr>
<tr>
<td style="width: 114px;">J&nbsp;</td>
<td style="width: 114px;">Smith</td>
<td style="width: 116px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
</tr>
<tr>
<td style="width: 114px;">John&nbsp;</td>
<td style="width: 114px;">Smith</td>
<td style="width: 116px;">50505050</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
</tr>
<tr>
<td style="width: 114px;">J Smith</td>
<td style="width: 114px;">&nbsp;</td>
<td style="width: 116px;">&nbsp;</td>
<td style="width: 117px;">77777777</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">50505050</td>
</tr>
<tr>
<td style="width: 114px;">Jane</td>
<td style="width: 114px;">Smith</td>
<td style="width: 116px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">77777777</td>
</tr>
<tr>
<td style="width: 114px;">J</td>
<td style="width: 114px;">Doe</td>
<td style="width: 116px;">65656565</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">33333333</td>
</tr>
<tr>
<td style="width: 114px;">Jessica</td>
<td style="width: 114px;">Doe</td>
<td style="width: 116px;">&nbsp;</td>
<td style="width: 117px;">33333333</td>
<td style="width: 117px;">65656565</td>
<td style="width: 117px;">&nbsp;</td>
</tr>
</tbody>
</table>

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);

You should add code for creating your table and for populating it with test data. It's good that you gave your failing query, but "without success" doesn't tell say much. I assume if it was successful, you wouldn't be posting this question. Does it return an error? Does it not give the expected output? Giving more details will help someone to help you. — UncleCarl
– UncleCarl, Commented Aug 7, 2018 at 17:10
In the test sample, i can visually see repeating telephone numbers. However when i run the code, i get 0 rows returned. — user1246856
– user1246856, Commented Aug 8, 2018 at 9:18

Daniel Koller · Accepted Answer · 2018-08-07 14:29:47Z

1

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.

answered Aug 7, 2018 at 14:29

Daniel Koller

262 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1246856 Over a year ago

Many thanks Daniel, your idea worked as advertised. I made minor amendments as to ignore null telephone numbers and only retain results whereby the count of occurrence is greater than 2. [tag] AS subquery1 WHERE phone is not null GROUP BY phone HAVING count(*) >1; [/tag] The only issue is i.e John Smith has the same telephone number under main phone and mobile phone, I would want to ignore him as a duplicate in this search. Do you have any suggestions?

Daniel Koller Over a year ago

You could first do a SELECT DISINCT * FROM (...) AS subquery1 and around that the statement with the GROUP BY. I don't think this is a very good approach. If your table gets bigger, two concurrent subquerys will have an impact wich is getting worse with the recordcount.

Daniel Koller Over a year ago

it would be more simple just remove the part with 'mainPhone' AS source,. Since the UNIQUE-syntax automatically removes duplicates, every distinct user/phone combination is counted only once.

Collectives™ on Stack Overflow

MySQL find value repeating in multiple columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related