How to return rows that have the same column values in MySql

Lets consider the following table-

ID Score
1  95

2  100

3  88

4  100

5  73

I am a total SQL noob but how do I return the Scores featuring both IDs 2 and 4? So it should return 100 since its featured in both ID 2 and 4

Answer

This is an example of a “sets-within-sets” query. I recommend aggregation with the having clause, because it is the most flexible approach.

select score
from t
group by score
having sum(id = 2) > 0 and -- has id = 2
       sum(id = 4) > 0     -- has id = 4

What this is doing is aggregating by score. Then the first part of the having clause (sum(id = 2)) is counting up how many “2”s there are per score. The second is counting up how many “4”s. Only scores that have at a “2” and “4” are returned.