CODEWITHSUNDEEP
mysqlsqldatabase
J. Lavoie
J. Lavoie

Reputation: 334

MySQL Random Limit Select with table join

I have a simple database (see image) and I'd like to retrieve 10 questions randomly with all their answers (between 2-4 answers per question). I've tried multiple queries but I can't seem to get it right.

Things I've tried:

SELECT qa.Answerid,qa.Questionid
FROM question_answer as qa
JOIN (SELECT q.Questionid FROM question q ORDER BY rand() LIMIT 10)
WHERE qa.Questionid = q.Questionid

select *
From question q,question_answer qa,answer a 
where qa.Questionid=q.Questionid and qa.Answerid=a.Answerid
ORDER BY rand() LIMIT 10

enter image description here

I need a result like the following:

AnswerId         QuestionId       ...        ...        ...

With a maximum of 10 random QuestionId and the number of AnswerId will vary depending of how many answer there is for the 10 random questions.

Thank you for any help.

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Use a subquery on question to get the 10 questions and then join in the answers:

select *
From (select q.*
      from question q
      order by rand()
      limit 10
     ) q join
     question_answer qa
     on qa.Questionid = q.Questionid join
     answer a 
     on qa.Answerid = a.Answerid;

Upvotes: 2

Related Questions