sql-servert-sqlrelational-division

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


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


Solution

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