GROUP CONCAT and GROUP BY using LEFT JOIN If no record exist Must return something

Question

I have 2 tables with below structures.

question table

id, audio, s_id
1, ad1, 500
2, ad2, 500
3, ad3, 500

answers table where q_id is FK of question table.

id, s_id, q_id, number, answer
1, 500, 1, 088888801, 1
1, 500, 3, 088888801, 3
1, 500, 1, 090078601, 2
1, 500, 2, 090078601, 4
1, 500, 3, 090078601, 2

Lets take this example for better understanding of above structure.

I have generated phone calls on 2 numbers (090078601, 088888801), They both have picked up the call. On call, There will be total of 3 questions that will play. As you can see the answer table, Number 088888801 has answered question 1 and 3, he has skip question number 2, if user skip a question, there will be no entry in answer table. But number 090078601 has answered all 3 questions.

I want to fetch all record but if a user has skipped the answer, I want to show that this number has skipped that question, Here is my query

SELECT r.number, GROUP_CONCAT(COALESCE(r.answer, 'skip')) as answer FROM questions LEFT JOIN  answers ON r.q_id= q.id WHERE q.s_id= 500 group by r.number order by q.id ASC

This query returns me below

number	answer
088888801	1,3
NULL	NULL
090078601	2,4,2

What I actually want is

number	answer
088888801	1,'skip', 3
090078601	2,4,2

NOTE: I want an optimized query because There can be around 24 questions and more than 200000 numbers, If each number answers 24 questions there will be 4.8M records in answer table. This is just for one S_ID, There will be more records for other s_id, But I want query only on s_id because s_id is unique for each survey that runs.

Answer 1

Neither MySQLl 8 nor MariaDB would produce a full NULL row.

As Joins only link existing Questions to answers

But if you have such a constellation, you simply can add a additional WHERE clause

CREATE TABLE questions 
    (`id` int, `audio` varchar(3), `s_id` int)
;
    
INSERT INTO questions 
    (`id`, `audio`, `s_id`)
VALUES
    (1, 'ad1', 500),
    (2, 'ad2', 500),
    (3, 'ad3', 500),
    (4, 'ad4', 500)
;

CREATE TABLE answers 
    (`id` int, `s_id` int, `q_id` int, `number` int, `answer` int)
;
    
INSERT INTO answers 
    (`id`, `s_id`, `q_id`, `number`, `answer`)
VALUES
    (1, 500, 1, 088888801, 1),
    (1, 500, 3, 088888801, 3),
    (1, 500, 1, 090078601, 2),
    (1, 500, 2, 090078601, 4),
    (1, 500, 3, 090078601, 2)
;

SELECT 
    r.number, GROUP_CONCAT(COALESCE(r.answer, 'skip')) AS answer
FROM
    questions q
        LEFT JOIN
    answers r ON r.q_id = q.id 
WHERE
    q.s_id = 500
GROUP BY r.number
#ORDER BY q.id ASC

  number | answer
-------: | :-----
    null | skip  
88888801 | 3,1   
90078601 | 2,4,2 

SELECT 
    r.number, GROUP_CONCAT(COALESCE(r.answer, 'skip')) AS answer
FROM
    questions q
        LEFT JOIN
    answers r ON r.q_id = q.id 
WHERE
    q.s_id = 500 AND r.id IS NOT NULL
GROUP BY r.number
#ORDER BY q.id ASC

  number | answer
-------: | :-----
88888801 | 3,1   
90078601 | 2,4,2 

db<>fiddle here

Answer 2

SELECT  number, 
        ( SELECT GROUP_CONCAT(COALESCE(r2.answer, 'skip')
                              ORDER BY q.id ASC)
                FROM questions q
                LEFT JOIN answers r2  ON r2.q_id = q.id
                                     AND r2.number = r1.number
        ) AS answer
    FROM  answers r1
    GROUP BY number
    ORDER BY number

-->

number      answer
88888801    1,skip,3
90078601    2,4,2