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.

Answer 1

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

Answer 2

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