In a T-SQL table of prizes awarded, how can I find all users that received two specific prizes?

Question

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:

1. Load two query results
2. Create User object for all user IDs present in either query
3. For each user ID, iterate over each table, and record if they're present in both or not
4. Export the list of users in both

However, 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)?

Answer 1

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.

Answer 2

You can use a join for this:

SELECT p1.[user] FROM prizes p1 INNER JOIN
prizes p2 ON p1.[user] = p2.[user] 
AND p1.prize='Gold' AND p2.prize = 'Silver'
GROUP BY p1.[user]

Answer 3

Depending on your index structure and distribution, this may lead to two seeks instead of a full table/CIX scan, and may also be a bit easier to grok:

SELECT UserId FROM dbo.Entries 
  WHERE Prize = 'Gold'
INTERSECT
SELECT UserId FROM Entries 
  WHERE Prize = 'Silver';

• Example db<>fiddle