Retrieve MySql rows with the same column value

Question

Let us consider the following table-

ID Score
1  95

2  100

3  88

4  100

5  73

I'm a complete SQL noob, but how do I return a score containing ID 2 and 4? So it should return 100 since it appears in both ID 2 and 4

Answer 1

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

This will select the rows with ID 2 and 4. The HAVING clause then ensures that we find both rows; if one of them is missing, the count will be less than 2.

This assumes id is the only column.

Answer 2

This is an example of a "collection within a collection" query. I recommend using the having clause for aggregation as it is the most flexible method.

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 does is aggregate by score. Then the first part of the having clause (sum(id = 2)) counts how many "2"s there are in each fraction. The second one is the number of "4". Only scores of "2" and "4" are returned.