How to join 2 tables without common key and randomly generate rows of second table

Question

I have 2 tables.. [Users] and [Questions]

Users Table has..

UserId Name
--     ----
1      Bob
2      Ang
3      Bill

And Question Table has..

QuestId Description CategoryId
------- ----------  --------
0       Question0   1
1       Question1   1
2       Question2   1
3       Question3   1
4       Question4   1
5       Question5   1
6       Question6   1
7       Question7   1
8       Question9   1
9       Question9   1
10      Question10  2

Now, what I want is, select 5 random questions for each [User].

I've tried this query..

SELECT [User].UserId,Name, QuestId, Description from Users OUTER APPLY 
(SELECT TOP 5 QuestId, Description FROM Question WHERE CategoryId=1 ORDER BY NEWID(), 
Question.Id) RandomQuestions

And it's resulting like to something like this..

UserId Name QuestId Description
------ ---- ------- -----------
1      Bob  2       Question2
1      Bob  3       Question3
1      Bob  6       Question6
1      Bob  8       Question8
1      Bob  9       Question9

2      Ang  2       Question2
2      Ang  3       Question3
2      Ang  6       Question6
2      Ang  8       Question8
2      Ang  9       Question9

3      Bill 2       Question2
3      Bill 3       Question3
3      Bill 6       Question6
3      Bill 8       Question8
3      Bill 9       Question9

The problem with this, the QuestId is being generated randomly but if you notice, each Users has the same generated random questions. I want each Users to have different set of random Questions.

May be this one help to you : [Link] https://stackoverflow.com/questions/580639/how-to-randomly-select-rows-in-sql — Shusang, Jan 08 '20 at 04:00
@Shusang I have been in there, that link helped me but it was just a simple randomized rows, no joining and other logic. — Jhe, Jan 08 '20 at 04:05
This sounds like a job for `cross apply`. Maybe this will help https://stackoverflow.com/questions/41030295/how-to-pick-random-records-with-t-sql — Nick.Mc, Jan 08 '20 at 04:09
Your existing query should returns a random sets of questions per user. — Squirrel, Jan 08 '20 at 04:22

score 3 · Accepted Answer · answered Jan 08 '20 at 04:27

3

Use this one:

SELECT UserId,Name,QuestId, Description
FROM   Users  a
CROSS apply (
SELECT TOP 5 Row_number() OVER(ORDER BY (SELECT NULL)) AS RowNo, *
            FROM   (SELECT TOP 5 QuestId, Description
                    FROM   Question  b
                    WHERE  a.UserId = a.UserId 
                    ORDER  BY Newid()) S
) cs

answered Jan 08 '20 at 04:27

Shusang

337
1
10

1

Hi @Shusang, your answer works perfectly. But I'm intrigued by it. Why is it whenever I remove the "a.UserId = a.UserId", the result is, each Users are having the same generated question again. Could you please explain why is that happening? Also, what is the purpose of Row_number()? Because when I remove it, nothing changes. Same result. – Jhe Jan 14 '20 at 08:16
1

Hi @Jhe, we have give reference of 'Users a' record to cross apply join with 'Quetion b' so all user based get top 5 result display. and no need to use Row_number() if you have not need. Only for use display user wise question number. – Shusang Jan 16 '20 at 12:11

score 0 · Answer 2 · answered Jan 08 '20 at 04:19

0

You need to random QuestionID like this

WHERE QuestId = (ABS(CHECKSUM(NEWID()) % 10)) + 1

answered Jan 08 '20 at 04:19

Nguyễn Văn Phong

13,506
17
39
56

How to join 2 tables without common key and randomly generate rows of second table

2 Answers2