select rows with a set of values (in multiple rows in Conjunction [And]) in sql Server

Question

Hi All,

Suppose I have a table like:

id............value
```````````````````
A............1
A............2
A............3
B............1

and so on.

so, If I want to select rows with Value either 1 OR 2 OR 3, I do this:

select id From table where value in (1,2,3)

But, what I want to do is, select id with AND instead of OR. I want it like

so If I want to select rows where Id has value 1, 2 AND 3, (in this case, resultant = A), how do I select that?

Thanks!
(I tried to do it myself, no luck, I tried to search on google, I realized I dint even know how to phrase the question! so if the question here doesnt agree with my actual doubt, feel free to change it)

Answer 1

select id 
from MyTable
where value in (1, 2, 3) 
group by id
having count(distinct value) = 3