Solved

MySQL - Select random records from related table

Posted on 2009-05-20
7
361 Views
Last Modified: 2012-08-13
I have 4 tables in a MySQL database, as per the attached txt document:
exam -< section -< question -< options

I would like a single MySQL query which will return all sections, 5 [or less] questions and all options related to that question, for a specific exam id. i.e.:

exam1     section 1      question 3    answer a
exam1     section 1      question 3    answer b
exam1     section 1      question 3    answer c
exam1     section 1      question 3    answer d
exam1     section 1      question 9    answer a
exam1     section 1      question 9    answer b
exam1     section 1      question 9    answer c
exam1     section 1      question 9    answer d
exam1     section 1      question 7    answer a
exam1     section 1      question 7    answer b
exam1     section 1      question 7    answer c
exam1     section 1      question 7    answer d
exam1     section 1      question 6    answer a
exam1     section 1      question 6    answer b
exam1     section 1      question 6    answer c
exam1     section 1      question 6    answer d
exam1     section 1      question 2    answer a
exam1     section 1      question 2    answer b
exam1     section 1      question 2    answer c
exam1     section 1      question 2    answer d
exam1     section 1      question 1    answer a
exam1     section 1      question 1    answer b
exam1     section 1      question 1    answer c
exam1     section 1      question 1    answer d
exam1     section 2      question 1    answer a
exam1     section 2      question 1    answer b
exam1     section 2      question 1    answer c
exam1     section 2      question 1    answer d
exam1     section 3      NULL          NULL    
exam1     section 2      question 1    answer a
exam1     section 2      question 1    answer b
exam1     section 2      question 1    answer c
exam1     section 2      question 1    answer d

db.txt
0
Comment
Question by:egxis
  • 3
  • 2
  • 2
7 Comments
 
LVL 9

Expert Comment

by:Rurne
ID: 24433771
SELECT `exam`.`description` as exam_name, `section`.`description` as section_name, `question`.`text` as question_name, `options`.`is_answer` as answer FROM
`exam` INNER JOIN `section` ON  `section`.`exam_id`=`exam`.`id`
INNER JOIN `question` ON `question`.`section_id`=`section`.`id`
INNER JOIN `options` ON `options`.`question_id`=`question`.`id`
ORDER BY `exam`.`description`, `section`.`description`, `question`.`text`, `options`.`is_answer`;
0
 
LVL 15

Expert Comment

by:oobayly
ID: 24433977
@Rurne
If you don't mind me saying, your query doesn't deal with requirement to show not more than 5 questions per section.
I'd played around with using a LIMIT clause in a subquery in the questions JOIN clause, but that's not supported:
http://dev.mysql.com/doc/refman/5.1/en/subquery-errors.html

This query would do the job perfectly if it weren't for this restriction in MySql. Removing the LIMIT clause allows the query to execute, but of course then it's effectively the same as Rurne's.

I'm afraid my only suggestion is to get all the results (using Rurne's statement) and then remove questions so that each section has a maximum of 5
SELECT 
  `exam`.`description`,
  `section`.`description`,
  `question`.`text`,
  `options`.`text`,
  `options`.`is_answer`
FROM `exam`
INNER JOIN `section` ON `section`.`exam_id`=`exam`.`id`
INNER JOIN `question` ON `question`.`section_id` IN (SELECT `section_id` FROM `question` WHERE `section_id` = `section`.`id` ORDER BY RAND() LIMIT 5)
INNER JOIN `options` ON `options`.`question_id`=`question`.`id`
ORDER BY `exam`.`description`, `section`.`description`, `question`.`text`, `options`.`is_answer`;

Open in new window

0
 
LVL 9

Expert Comment

by:Rurne
ID: 24434143
Indeed.  Using a subquery in a JOIN predicate is not SQL-92 compliant, and MySQL's abysmal execution planner doesn't process subqueries well in any case.  You're essentially forced to create a temporary heap table to perform the LIMIT.  You simply can't do it without the use of temporary tables or multiple queries to split the data set.

There is a way to perform this in one query, but I'm reticent to suggest it because of the performance hit you'd take.  You're simply much better off splitting this into two distinct queries.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:egxis
ID: 24434297
@oobayly:
I get an error "This version of MySQL does not yet support 'LIMIT & IN/ALL/ANY/SOME subquery'" on version 5.1.30, and is described below. Do you have a possible work-around?
http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html

@Rurne:
I'm OK with the performance hit, as this is not a frequently run query, and the convenience of a single query is my preferred option.

0
 
LVL 15

Expert Comment

by:oobayly
ID: 24435396
Sorry, that query won't work due to that error. The reason I posted it was to explain what I was trying to say, and in the hope that someone with a little more experience could create a work around. I think Rurne will be your best bet.
0
 

Author Comment

by:egxis
ID: 24445111
@Rurne:
If you think your solution would work, could you please post it?

0
 

Accepted Solution

by:
egxis earned 0 total points
ID: 24480625
Eventually gave up on having single SQL query and rather use a SP that creates the results required.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question