getting latest test results

I have a database with 3 tables.

The first table contains students with fields fldID, fldName, fldPIN etc.

The second table contains a list of questions the student must answer with fields studentID and questionID

The third table contains one row for each time a student has answered a question and has fields studentID, questionID, response and score. There may be multiple answers per question.

I would like a query that allows me to get a students name (via their PIN) and a list of questions they need to answer and also their most recent score per question. The questions that are unanswered would have a score of null.

I'm unsure how to do the joind for this.

Many thanks for any help.

Dave

I have since come up with this sql. Could you take a look and let me know if this is valid or really bad!! I was struggling to understand your posts so struggled on to come up with this.

SELECT
`tbldelegate`.`fldFirstN开发者_如何学Pythoname`,
`tbldelegate`.`fldSurname`,
`tbldelegatequestions`.`fldQuestionID`,
`latestScores`.`fldScore`
FROM
`tbldelegate`
Left Join `tbldelegatequestions` ON `tbldelegatequestions`.`fldDelegateID` = `tbldelegate`.`fldID`
Left Join (SELECT *
FROM
(SELECT  max(fldID) as maxID
FROM tblscoredata 
GROUP BY 
`tblscoredata`.`fldDelegateID`,
`tblscoredata`.`fldSection`,
`tblscoredata`.`fldQuestion`
) AS x INNER JOIN `tblscoredata` AS f ON f.fldID = x.maxID) AS `latestScores` ON `latestScores`.`fldQuestion` = `tbldelegatequestions`.`fldQuestionID`
WHERE
`tbldelegate`.`fldPIN` =  '11'

---

I am guessing the names of some of the tables, but something like this should do the trick.

SELECT s.fldName, sq.questionId, r.score 
FROM students s
INNER JOIN studentQuestions sq ON s.fldID = sq.studentID 
LEFT JOIN questionResponses r ON sq.questionID = r.questionID AND sq.studentID = r.studentId 
WHERE s.fldPIN = 12345;

Try this to get the latest, or unanswered result for each question the student has to answer (this is based on using auto_increment ids and newer records having a higher id value).

SELECT s.fldName, sq.questionID, r.score 
FROM students s
INNER JOIN studentQuestions sq ON s.id = sq.studentID
LEFT JOIN questionResponses r ON sq.questionId = r.questionID AND sq.studentID = r.studentId
LEFT OUTER JOIN questionResponses r2 ON sq.questionID = r2.questionID AND sq.studentID   = r2.studentID AND r.id < r2.id
WHERE r2.id IS NULL AND s.fldPIN = 12345

---

Gordons answer is excellent. However, since Dave was struggling with MAX I can't resist posting a variant using it. My guess is that Gordons answer has better performance and that his answer would probably be my choice of the two.

SELECT s.fldName, sq.questionID, r.score 
FROM students s
INNER JOIN studentQuestions sq ON s.fldID = sq.studentID 
LEFT JOIN questionResponses r ON sq.questionId = r.questionID AND sq.studentID = r.studentId
WHERE r.id = (SELECT MAX(id) FROM questionResponses r2 where r2.id = r.id)
AND s.fldPIN = 12345