CODEWITHSUNDEEP
sqlsql-serverintersectionintersect
Saurabh Palatkar
Saurabh Palatkar

Reputation: 3384

Find common values (Intersection) from one column of a table depending on the given values of second column of same table

I've following table which specifies the areas where suppliers deliver their products, with three columns as

ID   Supp_ID   Area_ID
1      a         P
2      a         R
3      a         T
4      a         s
.      .         .
.      .         .
5      b         R
6      b         T
7      b         V
.      .         .
.      .         .
8      c         Z
9      c         R
10     c         P
11     c         T
.      .         .
.      .         .
.      .         .

Now I want such a stored procedure such that if I pass Supp_IDs a,b,c to the SP, it should return me the Area_IDs R,T which are common in all the 3 suppliers. In short I've to perform intersection of Area_IDs for given Supp_IDs.

Currently what I am trying is as:

select Area_ID from Table_name where Supp_ID=a
INTERSECT
select Area_ID from Table_name where Supp_ID=b
INTERSECT
select Area_ID from Table_name where Supp_ID=c

The above code is good when I know there is exact three Supp_IDs But I am not able to find how to use above logic at run when there will be different numbers of Supp_IDs.

Now I am not able to find how should I write above SP.

Thanks in advance.

Upvotes: 3

Views: 7966

Answers (2)

Szymon
Szymon

Reputation: 43023

Use the following query. This will get all unique area ids for each supplier and select only those that are present N times.

DECLARE @param TABLE (supp_id int)

insert into @param values (1),(2),(3)

select Area_ID from 
    (select Area_ID from table_name t
    inner join @param p on p.supp_id = t.supp_id) x
group by x.Area_ID
having count(*) = (select count(*) from @param)

Upvotes: 0

roman
roman

Reputation: 117345

select Area_ID
from Table1
where Supp_ID in ('a', 'b', 'c')
group by Area_ID
having count(distinct Supp_ID) = 3

Or, to clarify where 3 comes from:

declare @Filter table(ID nchar(1) primary key)

insert into @Filter values ('a'), ('b'), ('c')

select a.Area_ID
from Table1 as a
where a.Supp_ID in (select t.ID from @Filter as t)
group by a.Area_ID
having count(distinct Supp_ID) = (select count(*) from @Filter)

sql fiddle demo

Upvotes: 4

Related Questions