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
| 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