MySQL - How to get all the results having all of the column values?

I want to get all of the id1 where id2 is equal to 2 and 3. Consider the sample table given below

Sample Table-

| id1 | id2 |
| --- | --- |
|  1  |  2  |
|  1  |  3  |
|  1  |  4  |
|  2  |  2  |
|  2  |  4  |

In this case, expected output is as mentioned below because 2 is only mapped to 2 and not to 3, and hence only 1 result comes in output.

Expected output-

| id1 |
| --- |
|  1  |

But when i run the following query it also gives id1 = 2 in result along with id1 = 1

Select id1 from table where id2 in (2,3) group by id1;

What is the correct query to get this output?

Solution

You can using HAVING to filter the data

Select id1,count(distinct id2) from table 
 where id2 in (2,3) 
 group by id1 having count(distinct id2) = 2