On Sun, Aug 10, 2008 at 10:54 AM, Kevin Waterson <[EMAIL PROTECTED]> 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. There are many ways you could do this. You didn't specify if you care which N questions you get or not, so I'll assume you don't. > 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); I'm not sure why you're using a LEFT JOIN, and you need to get the category_id in there if you want to use it. Once you do that, the example Peter Brawley showed will work, although it assumes you have something to sort by, e.g. you want the most recent N rows. You can also use the LEFT JOIN technique shown in the MySQL docs: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html. It also assumes you want to sort by something. And Baron's article(s) on this is good: http://www.xaprb.com/blog/2007/03/14/how-to-find-the-max-row-per-group-in-sql-without-subqueries/. Here's an example using the GROUP BY method to get the first 5 questions by ID: SELECT c.test_category_id, q.test_question_id, a.test_answer_id, COUNT(*) FROM test_categories c JOIN test_questions q ON (c.test_category_id = q.test_category_id) JOIN test_answers a ON (c.test_question_id = a.test_question_id) JOIN test_questions q2 ON (c.test_category_id = q2.test_category_id AND q2.test_question_id >= q.test_question_id) GROUP BY 1,2,3 HAVING COUNT(*) < 6 In your case, you might be able to just cheat it with some MySQL-specific LIMIT stuff: SELECT q.test_question_id, a.test_answer_id FROM test_categories c JOIN test_questions q ON (c.test_category_id = q.test_category_id) JOIN test_answers a ON (c.test_question_id = a.test_question_id) WHERE q.test_question_id IN ( SELECT test_question_id FROM test_questions q2 WHERE c.test_category_id = q2.test_category_id LIMIT 5 ) - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
