List:General Discussion« Previous MessageNext Message »
From:Perrin Harkins Date:August 11 2008 3:57am
Subject:Re: SELECT N records from each category
View as plain text  
On Sun, Aug 10, 2008 at 10:54 AM, Kevin Waterson <kevin@stripped> 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
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