E
E
Evgeny Romashkan2018-03-18 19:33:18
MySQL
Evgeny Romashkan, 2018-03-18 19:33:18

How it is possible to collect these requests in one?

Quiz site.
questions
5aae927176643034231371.png
table: answers table:
5aae922446318343016828.png
A temporary temp table is also created from user answers with the question_id and answer_id fields
(the ID of the question the user answered, and, in fact, the ID of the answer that the user chose for him)
I need to create such an sql query that will display a table from 4 fields:
The text of the question that the user answered - The text of the answer that the user chose - The value, whether the answer is correct (is_correct) - The text of the correct answer to this question.
All we have done so far is to make two queries, where the first one displays the first 3 required fields, and the second - the fourth field:
1) SELECT questions.question_text, answers.answer_text, answers.is_correct
FROM questions INNER JOIN (temp INNER JOIN answers ON temp.answer_id = answers.answer_id) ON questions.question_id = answers.question_id;
2) SELECT temp.question_id, answers.answer_text
FROM temp INNER JOIN answers ON temp.question_id = answers.question_id
WHERE answers.is_correct=1; //displays another question_id, but this will be removed in the final version.
Here, for example, is the output from these two requests:
5aae94a3ecf05931548136.png
I would be grateful if anyone can help, tell me how to combine this into one request. The problem is that both of them access the field answers.answer_text..

Answer the question

In order to leave comments, you need to log in

1 answer(s)
L
Lazy @BojackHorseman MySQL, 2018-03-18
@EvgeniiR

SELECT 
 q.question_text,
 user_a.answer_text AS user_answer,
 a.answer_text AS correct_answer
FROM questions q
JOIN answers a ON a.question_id = q.question_id AND a.is_correct = 1
LEFT JOIN temp AS tmp ON tmp.question_id = q.question_id
LEFT JOIN answers user_a ON user_a.answer_id = tmp.answer_id

substitute the desired names of the table with user responses and the field in this table containing the answer given by the user.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question