> 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
>
>
