?
Solved

MySQL - Select random records from related table

Posted on 2009-05-20
7
Medium Priority
?
373 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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 

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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…

585 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