I have two tables,
Table QUESTIONS have ID, QUESTION, CORRECT_ANSWER_ID
Table ANSWERS have ID, QUESTION_ID, ANSWER_ID
i need to take the total of correct answer where ANSWER_ID must be same with CORRECT_ANSWER_ID, how to do that?
CodePudding user response：
I am going to assume that your tables are structured somewhat like this:
Questions:
| ID (int) | Question (varchar) | Correct Answer ID (int) |
|---|---|---|
| 1 | Question Text 1 | 2 |
| 2 | Question Text 2 | 1 |
Answers:
| ID (int) | Question ID (int) | Answer ID (int) |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
If this is the case, then if we wanted to select the correct answer for question 1, we would want ID 2 from the Answers table, since it has an Answer ID of 2 and that corresponds to the correct answer ID from the Question table.
The following query should suffice:
select Questions.Question, Answers.AnswerID, Answers.ID
from Questions INNER JOIN Answers on Questions.ID = Answers.QuestionID
where Question.Correct_Answer_ID = Answer.AnswerID
If you then wanted the count of correct answers, simply use a COUNT() function in the above query:
select COUNT(*) from
Questions INNER JOIN Answers on Questions.ID = Answers.QuestionID
where Question.Correct_Answer_ID = Answer.AnswerID