1

I'm trying to write an sql statement that will return a question and all its corresponding answers. In the example below I've hard coded the subject_id but it would be set dynamically.

I want to limit the results to only 5 questions but if I just add LIMIT 5 it limits the whole data returned to only 5 which is not what I want.

SELECT q.id,q.question,a.question_id,a.answer, a.correct
FROM questions q
JOIN answers a ON q.id = a.question_id
WHERE q.subject_id = 18 
GROUP BY q.id,a.id

I'd also like to achieve something else but I'm not sure whether it would be better to implement it in the business logic (php). I want to return the correct answer for each question (a.correct = 1) plus a random 3 of the incorrect answers (correct = 0).

ekad
  • 14,436
  • 26
  • 44
  • 46
Lux Interior
  • 311
  • 4
  • 12

1 Answers1

2

For the first question: you will need to apply the LIMIT to the questions table separately in a subquery.

  SELECT q.id,q.question,a.question_id,a.answer, a.correct
    FROM (SELECT * FROM questions q WHERE q.subject_id = 18
          ORDER BY RAND() LIMIT 5) q
    JOIN answers a on q.id = a.question_id
GROUP BY q.id, a.id
mellamokb
  • 56,094
  • 12
  • 110
  • 136
  • Thanks for the quick response. The solution works fine except it always returns the same 5 questions (the first 5). I need to return random ones each time. From reading about the RAND function it will be quite a slow solution as the database will have over 10k of questions. Is there a more efficient solution that would both limit the questions and return random ones each time? – Lux Interior Apr 17 '12 at 01:07
  • Perhaps this programmatic solution would be better. What do you think? http://www.electrictoolbox.com/msyql-alternative-order-by-rand/ – Lux Interior Apr 17 '12 at 01:12
  • 10k isn't very many rows (especially since you will be filtering by `subject_id` first). You may want to try it out and see just how slow it really is. If you want really fast randomization, you could generate the random id's client-side with a solution like this: http://www.dasprids.de/blog/2008/06/07/fetching-random-rows-of-mysql-efficiently. Also take a look here: http://stackoverflow.com/questions/2707717/how-do-i-select-a-random-record-efficiently-in-mysql – mellamokb Apr 17 '12 at 01:12
  • Good point about the filtering by subject_id. That will bring the number of questions down considerably. Also the link is interesting too. – Lux Interior Apr 17 '12 at 01:35
  • I'm still having trouble trying to figure out the second part of my problem. Does anybody have any ideas? Thanks – Lux Interior Apr 17 '12 at 12:03
  • @SteveF Accepted answers tend to receive less attention. Maybe ask a new question. – Strawberry May 05 '17 at 12:53