List:General Discussion« Previous MessageNext Message »
From:Kevin Waterson Date:August 10 2008 2:54pm
Subject:SELECT N records from each category
View as plain text  
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