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.

## Solution 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)
)
``````