Choose X random users per item.

Question

I have 2 tables: Items and Users .

Items contains ItemID, and Num_Users
Users contains UserID

For each ItemID I need to randomly select the number of users according to the value specified in the Num_Users column.

I created a sample SQL Fiddle dataset.

Answer 1

One of the methods. SQL Fiddle

 SELECT Items.[ItemID], UserID FROM Items OUTER APPLY (SELECT TOP ([Num_Users]) * FROM Users ORDER BY Newid()) A 

You can also use CRYPT_GEN_RANDOM(4) instead of Newid() for more randomness, but there are some problems with this on non-modern versions of SQL Server 2008.