Help with query...

pearl2 · #1 (**permalink**) 01-19-04, 03:04

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?

r937 · #2 (**permalink**) 01-19-04, 09:14

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)

pearl2 · #3 (**permalink**) 01-20-04, 02:11

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.

r937 · #4 (**permalink**) 01-20-04, 02:38

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

pearl2 · #5 (**permalink**) 02-02-04, 08:45

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

Quote:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech