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

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?

CodePudding user response：

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

CodePudding user response：

Using aggregation, we can try:

SELECT id1
FROM yourTable
WHERE id2 IN (2, 3)
GROUP BY id1
HAVING COUNT(DISTINCT id2) = 2;