Checking if user submitted the correct answer

Question

I have 4 tables:

        `users`
+---------+--------+
|    id   | name   | 
+---------+--------+
|       1 |   John |
|       3 |   Sam  |
|       4 |   Doe  | 
+---------+--------+


   `questions`
+---------+------------+
|    id   | question   | 
+---------+------------+
|       1 |   Qe1      |
|       3 |   Qe2      |
|       4 |   Qe3      | 
+---------+------------+



                      `answers`
+---------+--------+-------------+--------------+
|    id   | answer | is_correct  | question_id  |
+---------+--------+-------------+--------------+
|       1 |   1234 |      0      |       1      |
|       3 |   2233 |      1      |       1      |
|       4 |   88   |      0      |       1      |
+---------+--------+-------------+--------------+

     
         `user_answers`

+---------+--------+---------+--------------+
|    id   | answer | user_id | question_id  |
+---------+--------+---------+--------------+
|       1 |   2514 |     1   |      1       |
|       2 |        |     1   |      2       |
|       3 |   8785 |     1   |      3       |
|       4 |    579 |     1   |      4       |
+---------+--------+---------+--------------+

I'm trying to fetch the name from users table. As well as is_correct from the user_answers table

My query:

SELECT c.name, GROUP_CONCAT(a.is_correct) AS correct
FROM user_answers ua 
JOIN users u 
ON u.id = ua.user_id
JOIN answers a
ON a.answer = ua.answer 
GROUP BY u.id;

There are 50 answers. But for some users, I get like 51 from GROUP_CONCAT and it should be 50.

The answer in user_answers could be empty, doesn't exist or exists. So:

• if ua.answer == a.answer, 1 or 0 returned.
• if ua.answer != a.answer, 0 returned.
• if ua.answer == '', 0 returned.

So the output will look like this:

Array
(
    [0] => Array
        (
            [name] => John
            [correct] => 0,1,1,1,1,1,0,1,1,1,1,1,1,1,1,1,0,1,1,1,1,1,0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
        )

    [1] => Array
        (
            [name] => Sam
            [correct] => 1,1,0,1,1,1,0,1,0,1,1,1,1,1,1,1,0,1,1,1,1,1,1,0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,1,1,1,1,0,1,1,1,1,1,1,1
        )

)