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

Solution 1:

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.

Solution 2:

SELECT score
FROM t
WHERE id in (2, 4)
HAVING COUNT(*) = 2 /* replace this with the number of IDs */

This selects the rows with ID 2 and 4. The HAVING clause then ensures that we found both rows; if either is missing, the count will be less than 2.

This assumes that id is a unique column.