0

Ok guys, im not very good in MySQL, but I can do basics... So now I need some help. I have two tables - questions(where some questions and avaliable answers are stored) and log(where log from the previus answered question is stored)

questions-

questionId,     question,                answer1...balbalba
1              'question balba'         'Avaliable answer1'
2              'question2 blaba'        'Other answer'

log-

id, questionId, userId
1   1           123456

Here is stored that userId is answered on questionId 1

So I need a query that finds random question from 'questions' that user '123456' isn't answered. I hope you help me...

Марин-Мемо Митрев
  • 275
  • 2
  • 12

1 Answers1

1
SELECT q.questionId
FROM questions AS q
LEFT OUTER JOIN log AS l ON q.questionId = l.questionId AND l.userId = 123456
WHERE l.id IS NULL

This gives you the questions that user 123456 has not answered.

To select one of them at random, append

ORDER BY RAND()
LIMIT 1

to the query.

The problem with ORDER BY RAND() is, that it does not scale well. However, the join condition already does a good job in reducing the size of the result set (it gives you at most one result per question). If the number of questions is low (around 100), I would not worry about performance implications of ORDER BY RAND(). If you have a lot of questions, have a look at question How can i optimize MySQL's ORDER BY RAND() function? for alternatives.

Community
  • 1
  • 1
Oswald
  • 31,254
  • 3
  • 43
  • 68
  • see [this question](http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function) for the performance implications of using `ORDER BY RAND()` – hd1 Mar 25 '13 at 20:01
  • @hd1 Thanks. I actually suspected that the performance is quite poor. – Oswald Mar 25 '13 at 20:11