t-sql select query filter

Question

Based on the below table and inputs:

Id  RelatedId
--------------
1   1
1   2
1   3
2   2
2   3 
2   4
3   5

Inputs are @input_1 = 2 and @input_2 = 3 (input count can vary)

I want to select only those Ids from the above table that have both these input in their corresponding RelatedIds.

So, based on the give inputs, output will be

Id
---
1
2

Thanks.

How are the inputs being passed? Is this a stored procedure? The fact that input count can vary is the trickiest problem. — Todd Owen, Jul 07 '10 at 09:57

SQLMenace · Accepted Answer · 2010-07-07T01:31:05.077

4

try

select id
from YourTable
where relatedid in ( @input_1, @input_2)
group by id
having count(*) >=2 -- for 3 inputs, make this 3 etc

example you can run

create table #yourtable(Id int,  RelatedId int)

insert #yourtable values(1,1)
insert #yourtable values(1,2)
insert #yourtable values(1,3)
insert #yourtable values(2,2)
insert #yourtable values(2,3)
insert #yourtable values(2,4)
insert #yourtable values(3,5)


declare @input_1 int, @input_2 int
select @input_1 = 2,@input_2 = 3

select id
from #yourtable
where relatedid in ( @input_1, @input_2)
group by id
having count(*) >=2

edited Jul 07 '10 at 01:31

answered Jul 07 '10 at 01:16

SQLMenace

132,095
25
206
225

Or "count(distinct relatedid)" if there's a possibility of duplicates. – Todd Owen Jul 07 '10 at 10:00

score 1 · Answer 2 · edited May 23 '17 at 10:24

Try this:

SELECT Id FROM tableName 
INNER JOIN (SELECT @input_1 AS id 
   UNION SELECT @input_2,
   UNION SELECT @input_3) inputs
ON inputs.id = tableName.Id

Source

Alternatively:

BEGIN
  DECLARE @inputs TABLE( id tinyint )
  INSERT INTO @inputs SELECT @input_1
  INSERT INTO @inputs SELECT @input_2
  INSERT INTO @inputs SELECT @input_3

  SELECT * FROM tableName
  INNER JOIN @inputs i ON i.id = tableName.Id
END

t-sql select query filter

2 Answers2