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.
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)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments