So, I am trying to make some operations more efficient from server side languages into SQL Server.
Imagine the following:
- a user can be registered for multiple classes
- for each class (module), there can be many exam questions
- and for each exam question, the user will answer the question
Seems simple?
Now, when we go to review all the user exams later on the website, it has to go through all modules and all exams the user has sat and for each exam question, checks to see if the student has the correct answer. Unfortunately this takes quite some time and is a perf issue (not on the SQL end).
anyway, to get to the point...
How can I write a query in SQL which will:
- take a module ID (or even better, a list of module ID's) along with the UserID
- for the modules, join to the ModuleExam table based on that moduleID
- join to the ModuleAnswer and UserModuleAnswer table based on the ModuleExamQuestionID
- check to see if the UserModuleAnswerID matches the ModuleAnswerID and if so, display 1 for a "IsCorrect" column else 0 for the column
I'm hoping I did not miss anything here but any help is a great starting point!