From: Peter Brawley
Date: August 10 2008 3:54pm
Subject: Re: SELECT N records from each category
List-Archive: http://lists.mysql.com/mysql/214031
Message-Id: <489F0F1C.3060009@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

 > 1) SELECT N questions and the related answers from each category.

See "Within-group quotas (Top N per group)" at 
http://www.artfulsoftware.com/infotree/queries.php.

PB

Kevin Waterson wrote:
> I have 3 tables (schema below) with categories, questions and answers.
> Each category can of course have many questions in it. Also, the
> answers are multiple choice, so each question can have several
> related answers. I am trying to achieve 2 goals.
>
> 1) SELECT N questions and the related answers from each category.
> The result needs to have say, 5 questions from each category.
> Of course, the answers for each of the questions needs to be there also.
>
> 2) SELECT N questions and related answers from specified categories.
> This time, a simple WHERE test_category_id IN(1,2,3) will do I think.
>
> I can select ALL the questions and related answers, but this is as far
> as I get. I think I need some group-wise additions.. or something
>
> SELECT
> test_question_id,
> test_question_text, 
> test_answer_id, 
> test_answer_text, 
> test_answer_correct 
> FROM test_questions q1  
> LEFT JOIN    
>    (SELECT    
>    test_answer_id,   
>    test_answer_text,   
>    test_question_id,  
>    test_answer_correct   
>    FROM   
>    test_answers)  
> AS q2  
> USING(test_question_id);
>
> The schema looks like this..
>
> CREATE TABLE test_categories (
>   test_category_id int(11) NOT NULL auto_increment,
>   test_category_name varchar(450) NOT NULL default '',
>   PRIMARY KEY  (test_category_id)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> CREATE TABLE test_questions (
>   test_question_id int(11) NOT NULL auto_increment,
>   test_category_id int(11) default NULL,
>   test_question_text varchar(254) NOT NULL,
>   test_question_code varchar(1024) NOT NULL,
>   PRIMARY KEY (test_question_id),
>   FOREIGN KEY (test_category_id) REFERENCES test_categories (test_category_id)
>   ON DELETE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> CREATE TABLE test_answers (
>   test_answer_id int(11) NOT NULL auto_increment,
>   test_question_id int(11) NOT NULL,
>   test_answer_correnct tinyint(4) NOT NULL,
>   PRIMARY KEY  (test_answer_id),
>   FOREIGN KEY (test_question_id) REFERENCES test_questions (test_question_id)
>   ON DELETE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> Any help in this matter hugely appreciated,
> Kind regards
> Kevin
>
>