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

Question

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 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.

Answer 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.

Answer 3

select Score
from tbl a
where a.ID = 2 -- based off Score with ID = 2
    --include Score only if it exists with ID 6 also
    and exists (
        select 1
        from tbl b
        where b.Score = a.Score and b.ID = 6
    )
    -- optional?  ignore Score that exists with other ids as well
    and not exists (
        select 1
        from tbl c
        where c.Score = a.Score and c.ID not in (2, 6)
    )