List the pair of customers who share the same phone number-CodePudding

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

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

This is one of my assignment paper question. I can't understand what the exactly answer they want?

I just created the following table:

Table

CodePudding user response：

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.

CodePudding user response：

SELECT * FROM Customer
WHERE custPhone IN(
    SELECT custPhone FROM Customer group by custPhone having count(custPhone) > 1
)
order BY custPhone;