Return only duplicate values from table

Question

I am new to sql and I am facing a problem. I have a table of call records which contains two columns Anumber and Bnumber. If any number calls, a new row is added to the table with Anumber as Source and Bnumber as Destination.

I have given two Anumber values (3217913664,3006307180) Now I have to find all the values from Bnumber (which was called by Anumber)

Let's say my table is :

ANUMBER        BNUMBER
-------        --------
3217913664     3006307180
3217913664     3212026005
3006307180     3212026005
3006307180     3212026007
3006307180     3212026008
3006307180     3212026009
3217913664     3212026009

Now I want to extract value(3212026005 and 3212026009) from Bnumber because both numbers were called by the given numbers. So I basically I have to extract only those numbers which were called by all the given number.

My English is not so good but I think I explained my problem. Any idea how can I achieve this scenario?

Answer 1

Here is one method:

select bnumber
from t
where anumber in (3217913664, 3006307180)
group by bnumber
having min(anumber) < max(anumber);

If the rows have no duplicates, then using count(*) = 2 is an alternative.

If you have more than 2 anumbers that you want to test, then use count(distinct anumber) = n, where n is the number of values in the in list.

Answer 2

You can try this one:

select  Bnumber
from    yourtable
group by Bnumber
having COUNT(Bnumber) = (SELECT COUNT(*) FROM (SELECT DISTINCT Anumber FROM yourtable) AS T)

this is a generic one. If you insert another Anumber in your table the query still works. Just one warning: Maybe this is not the best way to solve your problem, but I can't think in other query right now. Maybe later I would try to optimize the query.