List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 10 2008 3:54pm
Subject:Re: SELECT N records from each category
View as plain text  
 > 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
>
>   
Thread
SELECT N records from each categoryKevin Waterson10 Aug
  • Re: SELECT N records from each categoryPeter Brawley10 Aug
    • can some please help me -- REPLICATIONCharles Brown10 Aug
      • Re: can some please help me -- REPLICATIONJim Lyons10 Aug
        • Re: can some please help me -- REPLICATIONDavid Giragosian10 Aug
          • RE: can some please help me -- REPLICATIONCharles Brown10 Aug
          • RE: can some please help me -- REPLICATIONCharles Brown10 Aug
            • Re: can some please help me -- REPLICATIONMoon's Father12 Aug
    • Re: SELECT N records from each categoryKevin Waterson10 Aug
      • Re: SELECT N records from each categoryPeter Brawley11 Aug
        • Re: SELECT N records from each categoryKevin Waterson11 Aug
          • Re: SELECT N records from each categoryPeter Brawley11 Aug
  • Re: SELECT N records from each categoryPerrin Harkins11 Aug
    • Re: SELECT N records from each categoryKevin Waterson11 Aug