Question 22192 in Mysql Question posted in
The official MySQL documentation can be found here.

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?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    Using aggregation, we can try:

    SELECT id1
FROM yourTable
WHERE id2 IN (2, 3)
GROUP BY id1
HAVING COUNT(DISTINCT id2) = 2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search