SQL join have a double the column i want-CodePudding

Hello I was trying to make a new view by Joining 2 View. I have 1 view filled with a question id and the number of correct answer. Then another with the number of wrong answer. I want to have it merged so its just question_id, correct_answer, wrong_answer. Im using MySQL workbench to do my queries.

This the query for making the correct answer view

CREATE VIEW v_question_history_correct
    AS
SELECT question_id, COUNT(correctness) AS true_answer FROM skripsi_database_soal.questions_history
        WHERE correctness = 'true'
        GROUP BY question_id;

This is the query for making the wrong answer view

CREATE VIEW v_question_history_false
AS    
SELECT question_id, COUNT(correctness) AS false_answer FROM skripsi_database_soal.questions_history
    WHERE correctness = 'false'
    GROUP BY question_id;

This is the query i use to join both of them

SELECT * FROM v_question_history_correct JOIN v_question_history_false 
    ON v_question_history_correct.question_id = v_question_history_false.question_id;

This is What i get enter image description here

This is the content of correct answer enter image description here

This is the content of wrong answer enter image description here

Any help would be appreciated. To add Im still new to the JOIN stuff so I might have written a wrong syntax to begin with. Thanks

CodePudding user response：

Try this:

    SELECT coalesce(v_question_history_correct.question_id,f.question_id,v_question_history_false.question_id) 'question_id',true_answer,false_answer 
FROM v_question_history_correct 
    full JOIN v_question_history_false     ON v_question_history_correct.question_id = v_question_history_false.question_id;

CodePudding user response：

your data

CREATE TABLE v_question_history_correct(question_id int ,true_answer  int);
insert into v_question_history_correct
(question_id,true_answer) VALUES 
(5,7),
(6,8),
(7,8);

CREATE TABLE v_question_history_false(question_id int ,false_answer   int);
insert into v_question_history_false
(question_id,false_answer ) VALUES 
(2,7),
(1,7),
(6,1),
(7,1);

use full join equivalent in mysql

SELECT VC.question_id,true_answer ,false_answer 
FROM v_question_history_correct VC
LEFT JOIN v_question_history_false  VF
    ON VC.question_id = VF.question_id;
UNION
SELECT VF.question_id,true_answer ,false_answer 
FROM v_question_history_correct VC
RIGHT JOIN v_question_history_false  VF
    ON VC.question_id = VF.question_id;

dbfiddle