Solved

MySQL - Select random records from related table

Posted on 2009-05-20
7
356 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
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.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 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

27 Experts available now in Live!

Get 1:1 Help Now