Solved

MySQL - Select random records from related table

Posted on 2009-05-20
7
355 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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:egxis
Comment Utility
@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
Comment Utility
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
Comment Utility
@Rurne:
If you think your solution would work, could you please post it?

0
 

Accepted Solution

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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now