Get the employees who have more than one phone number with MySQL

user_14761254 0
Viewed 1326

I have two tables, one is employees and another one is details. Here I need the query to display the employees who are having multiple phone numbers.

Employees table:

employee_id   Name         salary
-----------  -------  --------------------
0001           John         100000
0002           Peter        50000
0003           Russel       60000
0004           Bill         60000
0005           Patrick      90000

Details Table :

employee_id   Address         phone
-----------  -------  --------------------
0001           USA         854646542
0002           Germany     656562354
0001           USA         465222333
0004           China       888444444
0005           Canada      012445869
0005           Canada      789875877
0003           Japan       444555807

From this I need to display employees who are having more than one phone number so the expected output should be

employee_id   Name         phone
-----------  -------  --------------------
0001           John         854646542
0001           John         465222333
0005           Patrick      012445869
0005           Patrick      789875877

Query I have tried :

SELECT COUNT(*)
FROM (
  SELECT employee_id, COUNT(*) AS CNT
  FROM details
  GROUP BY employee_id
) AS T
WHERE CNT > 1
mysql sql
3 Answers
SELECT DISTINCT employee_id, t3.Name, t1.phone
FROM Details t1
JOIN Details t2 USING (employee_id)
JOIN Employee t3 USING (employee_id)
WHERE t1.phone != t2.phone
-- ORDER BY employee_id
user_101387340

This uses a subquery

SELECT employee_id, Name, phone
FROM details
WHERE employee_id IN (
   SELECT employee_id
   FROM details
   GROUP BY employee_id
   HAVING COUNT(*) > 1
)

I can also be written using a JOIN:

SELECT d.employee_id, d.Name, d.phone
FROM
    details d
    INNER JOIN (
       SELECT employee_id
       FROM details
       GROUP BY employee_id
       HAVING COUNT(*) > 1
    ) p ON d.employee_id = p.employee_id

This can also be done using OVER clause

SELECT employee_id, Name, phone
FROM (
   SELECT 
      employee_id, Name, phone, 
      COUNT(*) OVER (PARTITION BY employee_id) AS phone_count
   FROM details
) t
WHERE t.phone_count > 1
user_45400200 
SELECT employee_id, t1.Name, phone
FROM details
JOIN employees t1 USING (employee_id)
WHERE employee_id IN (
   SELECT employee_id
   FROM details
   GROUP BY employee_id
   HAVING COUNT(*) > 1
)

This will get with name from 1st table

user_108741470
Related