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