Brian,
Somehow the server ate my response...
Re your schema,
--it's redundant to define PRIMARY and UNIQUE keys on the same column,
--why not an INT student id?
--what if two (eg married) students share an email account?
--comparing datetimes across multiple time zones will be simpler if you
set completed_modules.time=UTC_TIMESTAMP in each new row of that table.
That would give ...
CREATE TABLE students (
id INT NOT NULL, -- auto_increment [simplest] or assigned by
school?
email varchar(64) NOT NULL,
fname varchar(32) NOT NULL,
lname varchar(32) NOT NULL,
role char(2) NOT NULL default '5',
password varchar(8) NOT NULL,
phone varchar(24) default NULL,
reg_date date default NULL,
PRIMARY KEY (id),
KEY email (email)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE completed_modules (
id INT NOT NULL,
module_id char(2) NOT NULL default '',
score INT NOT NULL default 0,
time timestamp NOT NULL default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
To find the first 10 scores of 100 on a particular module, just ...
SELECT
CONCAT(s.lname,', ',s.fname) AS Name,
c.time,
c.score
FROM students s
INNER JOIN completed_modules c USING (id)
WHERE c.module_id = 1 AND c.score = 100
ORDER BY c.time ASC
LIMIT 10;
PB
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.1/347 - Release Date: 5/24/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
