0

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.

2 Answers 2

0

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

0
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.