Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: Help with query...

    Hi all,

    I don't seem to be getting the results I'm looking for with the query below:
    Code:
    SELECT scores.test_id, score, question_id
          FROM scores
          LEFT JOIN wrongs USING (member_id, subject, test_id)
          WHERE scores.member_id='1'
          AND scores.subject='English'
          ORDER BY scores.test_id DESC LIMIT 3
    The two tables being used store the following info:
    Code:
    scores
    member_id subject test_id score date 
    1                English 1        80      2004-01-17 
    1                English 2        40      2004-01-17 
    1                English 3        80      2004-01-17 
    1                English 4        80      2004-01-19 
    1                English 5        80      2004-01-19 
    1                English 6      100      2004-01-19 
    1                English 7        60      2004-01-19
    
    wrongs
    member_id subject test_id question_id 
    1                English 1         2 
    1                English 2         8 
    1                English 2         9 
    1                English 2        10 
    1                English 3        12 
    1                English 4        20 
    1                English 5        25 
    1                English 7        34 
    1                English 7        35
    The output I got from that query is as follows:
    Code:
    test_id score question_id
    7         60     34
    7         60     35
    6       100     NULL
    I was expecting something like:
    Code:
    test_id score question_id
    7         60     34
    7         60     35
    6         100   NULL
    5         80     25
    What I'm trying to do is selecting the last 3 tests from the table 'scores' with their corresponding wrong question numbers in the table 'wrongs'. How would the query be like?

    Could someone help to enlighten me?

    Updated:

    The following gets the results I need:
    Code:
    SELECT scores.test_id, score, question_id
          FROM scores
          LEFT JOIN wrongs USING (member_id, subject, test_id)
          WHERE scores.member_id='1'
          AND scores.subject='English'
          AND scores.test_id > 4
          ORDER BY scores.test_id DESC
    But is there a better way of writing the query?
    Last edited by pearl2; 01-19-04 at 04:21.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you are not on mysql 4.1, write two queries, the first to get the last 3 tests and the second to get the scores, using the ids from the first query in the WHERE clause with id IN (id1, id2, id3)

    if you are on 4.1, this can be done in one query with id IN (subquery)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks, r937!

    I'm using MySQL 4.0.16. I'm not sure if sub-query is supported in that version but I'll experiment with it later...

    I need to progressively select the tests in descending order (as in click a link to select first 10, then select the next 10, and so on). The following seems to work:
    Code:
    # using perl../
    $lower_bound = some_id;
    $upperbound = some_id;
    SELECT scores.test_id, score, question_id
          FROM scores
          LEFT JOIN wrongs USING (member_id, subject, test_id)
          WHERE scores.member_id='1'
          AND scores.subject='English
          AND scores.test_id > $lower_bound AND scores.test_id <= $upperbound
       };
    That code selects scores and wrong question IDs from tests that are above a certain id and up to the id defined by $upperbound.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    4.0.16 is less than 4.1 so you cannot use subqueries

    setting upper and lower bounds works just as nicely as listing the test numbers in an IN list

    now all you have to do is get the test numbers

    you said initially "the last 3 tests from the table 'scores'" and now you want to page through them, well, all you have to do is utilize the LIMIT keyword properly

    select test_id
    from scores
    where member_id='1'
    and subject='English
    limit 0,10

    the first time you run it, use LIMIT 0,10

    the second time you run it, use LIMIT 10,10

    then LIMIT 20,10 and so on

    after each time you run it, use the lowest and highest test_id numbers that were returned by that query to run your LEFT OUTER query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks, r937!

    Sorry I haven't been online for a while and didn't read your reply till today.

    I managed to get the desired results using lower and upper bounds. But I now have another option given in your solution using LIMIT. Will experiment with that.

    Cheers and thanks once again

    pearl2


    Originally posted by r937
    4.0.16 is less than 4.1 so you cannot use subqueries

    setting upper and lower bounds works just as nicely as listing the test numbers in an IN list

    now all you have to do is get the test numbers

    you said initially "the last 3 tests from the table 'scores'" and now you want to page through them, well, all you have to do is utilize the LIMIT keyword properly

    select test_id
    from scores
    where member_id='1'
    and subject='English
    limit 0,10

    the first time you run it, use LIMIT 0,10

    the second time you run it, use LIMIT 10,10

    then LIMIT 20,10 and so on

    after each time you run it, use the lowest and highest test_id numbers that were returned by that query to run your LEFT OUTER query

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •