3

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.

Saurabh Palatkar
  • 3,242
  • 9
  • 48
  • 107

2 Answers2

4
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

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • I would just clarify that the `3` comes from adding the amount of parameters in the `IN` clause. So unless the OP finds a way to count the amount of parameters sent to the SP, then they would have to add an extra parameter containing the amount of Supp_ID sent – Mosty Mostacho Oct 16 '13 at 06:16
  • 1
    @MostyMostacho thanks, added clarification to the answer, hope it helps – Roman Pekar Oct 16 '13 at 06:18
0

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)
Szymon
  • 42,577
  • 16
  • 96
  • 114