Need to find a random record from one table, then join another table with this record

Question

Background: I'm currently developing a simple online quiz. After the user has answered a simple question, the user gets the chance to improve their chances of winning by doing small tasks.

Every lot the user earns is inserted into a separate row, so when I need to find a "random" lot, their chances of winning are increased (please correct me if I'm wrong).

Sample data from the lots table:

LotId      ParticipantId      Created
1          1                  2012-11-16 12:00:00
2          2                  2012-11-16 12:02:00    
3          2                  2012-11-16 12:06:00 
4          2                  2012-11-16 12:15:00    
5          3                  2012-11-16 12:16:00

This means that the participant with ParticipantId 2 has three lots, and therefore a statistically bigger chance of winning compared to the other participants.

What I need to do: When I need to draw a winner, I select a random lot from the lots table. After this I need to find the information on the winner.

Currently I use this following SELECT (any comments on improving the SELECT are appreciated), and then later in my code I have another SELECT with the user's information.

SELECT TOP 1 LotId, ParticipantId FROM Sample_Lots WITH (NOLOCK)
WHERE
    CAST(Created AS DATE) = '2012-11-16'
    AND
    ParticipantId NOT IN
    (
        SELECT ParticipantId FROM Sample_Winners WITH (NOLOCK)
        WHERE
            ParticipantId IS NOT NULL
            AND
            CAST(ThisDate AS DATE) = '2012-11-16'
    )
    AND
    ParticipantId IN
    (
        SELECT ParticipantId FROM Sample_Participants WITH (NOLOCK)
        WHERE
            ParticipationConfirmed IS NOT NULL
    )
ORDER BY NEWID()

I can't work my head around this. I need a random lot, and from this random lot I need to get the user's information in the same SELECT. Any ideas?

It's possible that I can use a simple join between the two tables - but then how do I ensure that it's form the lots table it orders by "randomly" (NEWID())? — KlaasJan, Nov 16 '12 at 15:18

score 0 · Accepted Answer · answered Nov 16 '12 at 15:34

0

Assuming your user information is found in a table named Participants, with columns ParticipantId, FirstName, and LastName, here is an example using a JOIN to that table:

SELECT TOP 1 sl.LotId, sl.ParticipantId, p.FirstName, p.LastName 
FROM Sample_Lots sl 
INNER JOIN Participants p ON sl.ParticipantId = p.ParticipantId WITH (NOLOCK)
WHERE
    CAST(Created AS DATE) = '2012-11-16'
    AND
    sl.ParticipantId NOT IN
    (
        SELECT ParticipantId FROM Sample_Winners WITH (NOLOCK)
        WHERE
            ParticipantId IS NOT NULL
            AND
            CAST(ThisDate AS DATE) = '2012-11-16'
    )
    AND
    sl.ParticipantId IN
    (
        SELECT ParticipantId FROM Sample_Participants WITH (NOLOCK)
        WHERE
            ParticipationConfirmed IS NOT NULL
    )
ORDER BY NEWID()

answered Nov 16 '12 at 15:34

Simon Kingston

495
2
15

If I do this, then how do I now which table is ordered BY NEWID()? I need to make sure that it's the lots that gets randomly ordered by. If it's on the other hand the Participants that just gets order randomly, then there would be no need for the lots. I hope this makes sense :-) – KlaasJan Nov 16 '12 at 15:50
Assuming ParticipantId is unique in table Participants, then your whole joined result set (which will include as many records as exist in Sample_Lots for each participant, assuming every participant in table Sample_Lots is represented by a record in table Participants) should be ordered randomly. – Simon Kingston Nov 16 '12 at 16:13
Correct that ParticipantId is unique in Participants. However, a participant might have 20 records in Sample_Lots representing the 20 extra lots the participant has earned. On the contrary can some participants only have 1 or 2 lots, if they haven't earned any extra lots. That's why it's so important for me to ORDER BY a random lot than a random participant. – KlaasJan Nov 16 '12 at 16:26
When you join from Participants to Sample_Lots, if you have 20 records in Sample_Lots for Participant A and one record in Sample_Lots for Participant B, then the result set will have 20 records for Participant A and one record for Participant B. These are ordered randomly (by NEWID() for the result set) and one is selected (by the TOP 1). – Simon Kingston Nov 16 '12 at 16:30
That's perfect! Thanks for the explanation - I never quite understood the whole ORDER BY and TOP 1. Thanks for the help! – KlaasJan Nov 16 '12 at 16:48

score 0 · Answer 2 · answered Nov 16 '12 at 15:38

In your first select, do a left join of Sample_Lots to your Participant table on the ParticipantId columns. Then in your first select you can also get details about the participant. The result will have the same number of rows as your original select and will be ordered randomly.

Need to find a random record from one table, then join another table with this record

2 Answers2