MySQL, how to get rows that have certain columns

Question

I have a table like this:

|  ID1   |  ID2  |
------------------
|   1    |   1   |
|   2    |   1   |
|   3    |   1   |
|   4    |   1   |
|   5    |   1   |
|   6    |   1   |
|   7    |   1   |
|   8    |   1   |
|   9    |   1   |
|   10   |   1   |
|   11   |   1   |
|   12   |   1   |
|   13   |   1   |
|   1    |   2   |
|   2    |   2   |
|   5    |   2   |
|   1    |   4   |
|   9    |   4   |

Some of the ID1s have both the 1 and 4 values like this:

And I would like to return the ID1s like this:

|  ID1   |
----------
|   1    |
|   9    |

I have tried some basic queries but Im not even close.. I just cannot get this to work even remotely and I need some serious help with this one.

Answer 1

If ID1 is supposed to have only one unique value then the following query would work for you:

SELECT 
 ID1
FROM your_table
GROUP BY ID1
HAVING COUNT(DISTINCT ID2) > 1;

EDIT: May be you are looking for those entries having ID2 as 1 and 4 both.

SELECT 
 ID1
FROM your_table
WHERE ID2 IN (1,4)
GROUP BY ID1
HAVING COUNT(DISTINCT ID2) = 2;

Note: It would have been better if you clarify what combination of ID1 & ID2 is supported in your table.

Answer 2

Try this:

select id1
from yourtable
where id2 in (1,4)
group by id1
having count(distinct id2) = 2

Demo Here