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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
