This is not homework - it's based on a real problem I'm working on, but I've changed up the column names and values to create an MCVE.
I have a table of entries, where each row is a record of a prize won by a user. For sake of discussion, User
is an integer value and Prize
is a string. I'm using T-SQL.
User | Prize |
---|---|
1 | Gold |
1 | Silver |
1 | Bronze |
2 | Bronze |
3 | Gold |
4 | Silver |
I'd like to get a list of Users that have won both a Gold
prize and a Silver
prize.
I've started by filtering to entries containing Gold or Silver, but I'm not sure how to do "AND" here.
SELECT * FROM Entries WHERE Prize LIKE 'Gold' OR Prize LIKE 'Silver'
If I replace OR
with AND
, I expect get nothing as Prize
is only one or the other. I could just get the list of Silver
and Gold
recpipients, and use Python to do this:
User
object for all user IDs present in either queryHowever, I'm not sure how I can do that in SQL. Ideally, this would be my "result":
User |
---|
1 |
How can I get a list of Users that have won Gold and Silver prizes (it's fine if they've gotten other prizes, too)?
One canonical approach uses aggregation:
SELECT User
FROM Entries
WHERE Prize IN ('Silver', 'Gold')
GROUP BY User
HAVING COUNT(DISTINCT Prize) = 2;
-- HAVING MIN(Prize) <> MAX(Prize)
I have given an alternative HAVING
clause above for this query which might be sargable and more performant.