Question 113080 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – List the pair of customers who share the same phone number

This is my table
Customer (custid, custName, custAddress, custPhone)

My Question is
List the pair of customers who share the same phone number.

I just created the following table:

Table

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    SELECT * FROM Customer
WHERE custPhone IN(
    SELECT custPhone FROM Customer group by custPhone having count(custPhone) > 1
)
order BY custPhone;

    Table Data click here

    Query Data click here


  2. 0

    Let’s assume that all the phone numbers in your table are "spelled" the same way. For example, let’s assume that +1.212.555.1212 and (212)555-1212 are different phone numbers, even though they, according to the North American Dialing Plan, reach the same telephone. Every country has similar alternative phone number "spellings".

    Handling phone numbers in the real world is a giant hairball. Read Falsehood Programmers Believe About Phone Numbers. But, let’s leave that aside.

    You should start with a subquery to find all the numbers that appear more than once. This is it

                     SELECT custPhone
                   FROM Customer
                  GROUP BY custPhone
                 HAVING COUNT(*) > 1

    You’re a student. You should strive to completely understand this subquery. Start by running it on your table. HeidiSQL is good for this kind of thing.

    Then, use the subquery in a main query.

    SELECT Customer.*
  FROM Customer
  JOIN (
                 SELECT custPhone
                   FROM Customer
                  GROUP BY custPhone
                 HAVING COUNT(*) > 1
       ) multiple ON Customer.custPhone = multiple.custPhone
 ORDER BY custPhone, custName

    Pro tip Avoid mixed case in database, table, and column names. It reduces the portability of your data from one server to another.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search