Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL - Select random records from related table

Posted on 2009-05-20
7
Medium Priority
?
370 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

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.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

916 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