SQL query for same phone number
Home > IT Answers > Development > SQL query for same phone number
Iamnfo
5 pts.
0
Q:
SQL query for same phone number
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: Mar 8 2009  1:07 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Kccrosser
1835 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
Something like this.

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



------------- 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
Last Answered: Oct 16 2009  7:05 PM GMT by Kccrosser   1835 pts.
Latest Contributors: Mrdenny   46765 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   29770 pts.  |   Mar 9 2009  5:52PM GMT

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.

 

Kccrosser   1835 pts.  |   Oct 20 2009  4:16PM GMT

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.

 
0