georgerobbo Posted April 15, 2011 Share Posted April 15, 2011 I'm currently working on a multiple choice online examination app and I need to get the assignments given to a student. They are set an assignment and foreign keys to the AssignmentID (primary key) and UserID are stored in the table AssignmentsUsers. Questions are selected by random, (depending on the QuestionQuantity) entity in the Assignments table and foreign keys are stored in three tables, Results, ResultsSessions, ResultsAssignments. This is so during and after the examination, we can see which questions the students took, at what time they answered, what answer they selected and what assignment they were sitting. With my current query, I'm trying to get a list assignments set for the student (who is signed in) and tell whether they have completed each assignment by using a JOIN statement with the Results table. If ResulID is NULL, we can tell that the assignment hasn't be started. However, if it has been started - I'm getting multiple results as for each answer, the ResultsAssignments table contains two foreign keys. ResultsAssignments ResultID AssignmentID Name Slug DueDate 1 1 Networks networks 2011-04-15 13:35:00 2 1 Networks networks 2011-04-15 13:35:00 3 2 Policies policies 2011-04-18 13:35:00 SELECT Results.ResultID, Questions.TheQuestion, Answers.TheAnswer FROM Results JOIN ResultsAssignments ON ResultsAssignments.ResultID = Results.ResultID JOIN Assignments ON Assignments.AssignmentID = ResultsAssignments.AssignmentID JOIN AssignmentsUsers ON AssignmentsUsers.AssignmentID = Assignments.AssignmentID JOIN Users ON Users.UserID = AssignmentsUsers.UserID JOIN SessionsUsers ON SessionsUsers.UserID = Users.UserID JOIN Questions ON Questions.QuestionID = Results.QuestionID JOIN QuestionsAnswers ON QuestionsAnswers.QuestionID = Questions.QuestionID JOIN Answers ON QuestionsAnswers.AnswerID = Answers.AnswerID WHERE SessionsUsers.SessionID = '30c65fa97d8f3997761c1e01bb310475bce9a572' AND Assignments.Slug = 'computer-networks-and-the-internet' AND Assignments.AssignmentID = 1 AND Results.ResultID = 1 Assignments AssignmentID (Primary Key) Name Slug Difficulty (Integer) TimeLimit (TimeDate) QuestionQuantity (Number of Questions) DueDate (Date which the assignment must be complete by) AssignmentsUsers AssignmentID UserID Users UserID Firstname Surname Hash SessionsUsers UserID SessionID Results ResultID QuestionID AnswerID AnswerStamp ResultsAssignments ResultID AssignmentID ResultsSessions ResultID SessionID Quote Link to comment Share on other sites More sharing options...
kickstart Posted April 15, 2011 Share Posted April 15, 2011 Hi Can have a play, but to save me a load of typing could you export the table declarations and a small sample of data that can demonstrate the problem. However to me it looks like you just have several results for an assignment. Which I presume is correct as I presume an assignment has several questions each of which has a result. All the best Keith Quote Link to comment Share on other sites More sharing options...
georgerobbo Posted April 15, 2011 Author Share Posted April 15, 2011 Thanks! [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
kickstart Posted April 15, 2011 Share Posted April 15, 2011 Hi I changed the SQL a touch to do a SELECT * (not recommended for production use) to get all the columns and I can't see any duplicates. If I remove the WHERE clause for the session_id I can get a duplicate, but otherwise it seems fine. All the best Keith Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.