SQL query for same phone number

5 pts.
Tags:
SQL
SQL queries
SQL Query
SQL statements
I have 3 columns phone number, users and locations I would like to see only the name and phone numbers of the users that are calling the same phone number Not quite sure how to represent this in a SQL Statement Thank you very much for your assistance
Asked: Last updated:
Related Questions
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Something like this.

<pre>SELECT *
FROM YourTable
JOIN (SELECT Phone, count(*) ct
FROM YourTable
GROUP BY Phone
HAVING count(*) <> 1) a ON YourTable.Phone = a.Phone</pre>

————- kccrosser
I am assuming that your question is: How do I list, by phone number, all those people where two or more people have called the same phone number?

Assume the table is as follows:
table PhoneCalls (
NumberCalled varchar(10) not null, — simple for example
CallerName varchar(255) not null,
Location varchar(255));

My approach to these kinds of queries is to break it down into a subquery (find all numbers called by two or more people) and then use that to produce the result list, like:

— find numbers called by 2 or more people
select distinct NumberCalled
from PhoneCalls P1
where exists (select 1 from PhoneCalls P2
where P2.NumberCalled = P1.NumberCalled and P2.CallerName <> P1.CallerName)

— Now, use the results of that to get the output list:

select PResult.*
from PhoneCalls PResult,
(s

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    I think we could provide better help if you provide some example data, and show us how the output of the query you are looking for would be.
    85,390 pointsBadges:
    report
  • Kccrosser
    Ok - this is frustrating. Each time I try to finish the above answer I get a page cannot be displayed... So - the finish is: -- Now, use the results of that to get the output list: select PResult.* from PhoneCalls PResult, (select distinct NumberCalled from PhoneCalls P1 where exists (select 1 from PhoneCalls P2 where P2.NumberCalled = P1.NumberCalled and P2.CallerName <> P1.CallerName)) I think this will work better than MRDenny's approach. First, it will not return cases of multiple calls to the same number from the same person (unless there are also calls to that number from other person(s)). Second, a "where exists" clause can terminate as soon as the first record meeting the criteria is found, while the "count" expression requires that all such records must be fetched. Note the "distinct" in the subquery - the objective is to reduce the subquery return set before joining with the outer table. This will minimize the main join/sort processing at that level.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: