Return test answers for each user including users with no answers-CodePudding

I have 5 tables:

    users
 ----------- 
| id | name |
 ----------- 
|  1 | John |
 ----------- 
|  2 | Doe  |
 ----------- 

    tests
 ----------- 
| id | test |
 ----------- 
|  1 |  t1  |
 ----------- 

         questions
 ---- ---------- --------- 
| id | question | test_id |
 --------------- --------- 
|  1 |    q1    |    1    |
 --------------- --------- 
|  2 |    q2    |    1    |
 --------------- --------- 

                  answers
 ---- -------- ------------ ------------- 
| id | answer | is_correct | question_id |
 ---- -------- ------------ ------------- 
|  1 |    a1    |    1     |      1      |
 --------------- ---------- ------------- 
|  2 |    a2    |    0     |       1     |
 --------------- ---------- ------------- 

                 user_answers
 ---- --------- --------- ------------- ----------- ---------- 
| id | user_id | test_id | question_id | answer_id | duration |
 ---- --------- --------- ------------- ----------- ---------- 
|  1 |    1    |    1     |      1     |      1    |     1    |
 -------------- ---------- ------------ ----------- ---------- 
|  2 |    1    |    1     |      2     |      2    |     1    |
 -------------- ---------- ------------ ----------- ----------

I'm trying to get the name of the user, the correct/incorrect answer and total duration.

So if there are 5 questions for the test, I would get something like:

 ------ ----------- ---------- 
| name |  correct  | duration |
 ------ ----------- ---------- 
| John | 1,0,1,1,0 |      5   |
 ------ ----------- ---------- 
| Doe  | 0,0,0,0,0 |      0   |
 ------ ----------- ----------

Or:

 ------ ----------- ---------- 
| name |  correct  | duration |
 ------ ----------- ---------- 
| John | 1,0,1,1,0 |     5    |
 ------ ----------- ---------- 
| Doe  |     0     |     0    |
 ------ ----------- ----------

The SQL query I'm trying with:

SELECT u.name, GROUP_CONCAT(COALESCE(a.is_correct, 0)) AS correct, SUM(ua.duration) AS duration 
FROM user_answers ua
RIGHT JOIN users u 
ON u.id = ua.user_id 
RIGHT JOIN answers a 
ON a.question_id = ua.question_id
JOIN questions q
ON q.id = a.question_id
JOIN tests t
ON t.id = q.test_id
WHERE t.id = 1 GROUP BY u.id

It's just returning the user who answered the questions. I want to get the other user too who didn't answer the questions

Here is a fiddle with data and the SQL query: http://sqlfiddle.com/#!9/6a806a/3

CodePudding user response：

If I understand correctly, you can try to add ua.question_id IS NULL when you do JOIN on answers table, because some people might not write any answer but you want to show data.

I would suggest you redesign your answers table which might need to store the right answer values in that.

Query 1:

SELECT u.name, GROUP_CONCAT(IF(ua.answer = a.answer,1,0)) AS correct 
FROM user_answers ua
RIGHT JOIN users u 
ON u.id = ua.user_id 
 JOIN answers a 
ON (a.question_id = ua.question_id) OR ua.question_id IS NULL
JOIN questions q
ON q.id = a.question_id
JOIN tests t
ON t.id = q.test_id
WHERE t.id = 1 AND is_correct = 1
GROUP BY u.name

Results:

| name |             correct |
|------|---------------------|
|  Doe | 0,0,0,0,0,0,0,0,0,0 |
| John | 0,0,0,0,1,0,0,1,0,0 |

CodePudding user response：

for quick edit of the existing query, we can join it with the users table and use case when to set the null record to be like that

SELECT users.name, CASE WHEN correction.correct IS NULL THEN '0' ELSE correction.correct END as correct from 
(SELECT u.name,u.id, GROUP_CONCAT(COALESCE(a.is_correct, 0)) AS correct 
FROM user_answers ua
RIGHT JOIN users u 
ON u.id = ua.user_id 
RIGHT JOIN answers a 
ON a.question_id = ua.question_id
JOIN questions q
ON q.id = a.question_id
JOIN tests t
ON t.id = q.test_id
WHERE t.id = 1 GROUP BY u.id )  correction
right join users on users.id = correction.id

fiddle with query http://sqlfiddle.com/#!9/cf4c84/67

CodePudding user response：

Start with all possible combinations of users, questions and correct answers. Left join real answers

SELECT u.name,  GROUP_CONCAT(case when ua.id is null then 0 else 1 end order by q.id) AS correct 
from questions q
cross join users u 
join answers a on a.question_id = q.id and a.is_correct = 1
left join user_answers ua
   on q.id = ua.question_id 
      and u.id = ua.user_id 
      and ua.answer = a.answer
WHERE q.test_id = 1 
GROUP BY u.id, u.name

Note the order by q.id clause. You may want a different ordering.

Returns

name    correct
John    0,0,0,0,1,0,0,1,0,0
Doe     0,0,0,0,0,0,0,0,0,0