Hi
I'm trying to write a query to return a specific set of results from the database.
A selection of the content of my tables is:
quiz Table
+----+--------+----------+-------+
| id | course | name | grade |
+----+--------+----------+-------+
| 3 | 2 | Name 1 | 50 |
| 4 | 2 | Name 2 | 50 |
| 5 | 3 | Name 3 | 50 |
| 6 | 5 | Name 4 | 50 |
| 7 | 4 | Name 5 | 50 |
| 8 | 3 | Name 6 | 50 |
+----+--------+----------+-------+
quiz_grades table:
+------+--------+-------+
| quiz | userid | grade |
+------+--------+-------+
| 3 | 2 | 24 |
| 4 | 2 | 39 |
| 5 | 2 | 6 |
| 6 | 2 | 48 |
| 7 | 2 | 45 |
| 8 | 2 | 36 |
+------+--------+-------+
quiz_attempts table
+------+--------+---------+
| quiz | userid | attempt |
+------+--------+---------+
| 3 | 2 | 1 |
| 4 | 2 | 1 |
| 5 | 3 | 1 |
| 7 | 2 | 1 |
| 3 | 2 | 2 |
| 5 | 2 | 2 |
| 6 | 2 | 1 |
| 7 | 2 | 2 |
| 8 | 2 | 1 |
+------+--------+---------+
I want my query to return the following data for a given user id (in this case 2), and where quiz_grades.quiz = 3, 4, 6 OR 7:
+------------+-------------------+---------+
| quiz.grade | quiz_grades.grade | attempt |
+------------+-------------------+---------+
| 50 | 24 | 2 |
| 50 | 39 | 1 |
| 50 | 48 | 1 |
| 50 | 45 | 2 |
+------------+-------------------+---------+
So it will be restricted to only records which have the highest attempt number in the quiz_attempts table.
How do I write this query? I currently have:
mysql_query("SELECT quiz_grades.grade AS score, quiz.grade AS total, quiz_attempts.attempt AS attempt FROM (quiz_grades LEFT JOIN quiz ON quiz_grades.quiz = quiz.id) LEFT JOIN quiz_attempts ON quiz_grades.quiz = quiz_attempts.quiz WHERE (quiz_grades.quiz = '3' OR quiz_grades.quiz = '4' OR quiz_grades.quiz = '6' OR quiz_grades.quiz = '7') AND quiz_grades.userid = %d ", $uid);
This is returning the quiz attempts numbered 1 and 2, so I get extra rows (where a quiz has been attempted x times, I get x rows for that quiz). How do I restrict it to just the last attempt?
Thanks
Glenn
Hi Glenn,
This is the forum for the Moodle activity called database (quite unfortunate name giving IMO).
You might have a look at Database FAQ or pose your question in the General Developer forum (http://moodle.org/mod/forum/view.php?id=55).
hth
Frank
This is the forum for the Moodle activity called database (quite unfortunate name giving IMO).
You might have a look at Database FAQ or pose your question in the General Developer forum (http://moodle.org/mod/forum/view.php?id=55).
hth
Frank
Hi Glenn,
How about something like:
How about something like:
SELECT quiz_grades.grade AS score, quiz.grade AS total, quiz_attempts.attempt AS attempt
FROM (quiz_grades
LEFT JOIN quiz ON quiz_grades.quiz = quiz.id)
LEFT JOIN quiz_attempts ON quiz_grades.quiz = quiz_attempts.quiz
WHERE (quiz_grades.quiz IN ('3','4','6','7')
AND quiz_grades.userid = $USERID
AND quiz_attempts.attempt = (SELECT MAX(attempt) FROM quiz_attempts WHERE quiz_attempts.quiz=quiz.id AND quiz_attempts.userid=$USERID)
Not sure how efficient this is, but it may do the trick.
Ian