All geek questions in one place

MySQL query is joined by 4 tables according to different criteria

I have 4 tables (shortened to corresponding columns for short):

CREATE TABLE `papers` (
   `paper_id` int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (`paper_id`)
);
INSERT INTO papers ( paper_id ) VALUES(1001);
INSERT INTO papers ( paper_id ) VALUES(1002);   
INSERT INTO papers ( paper_id ) VALUES(1003);
INSERT INTO papers ( paper_id ) VALUES(1004);
INSERT INTO papers ( paper_id ) VALUES(1005);
INSERT INTO papers ( paper_id ) VALUES(1006);

CREATE TABLE `questions` (
  `question_id` int(11) NOT NULL AUTO_INCREMENT,
  `type_id` int(11) NOT NULL,
  PRIMARY KEY (`question_id`)
);
INSERT INTO questions ( type_id ) VALUES(1);
INSERT INTO questions ( type_id ) VALUES(2);
INSERT INTO questions ( type_id ) VALUES(1);
INSERT INTO questions ( type_id ) VALUES(3);

CREATE TABLE `question_depends` (
  `question_id` int(11) NOT NULL,
  `depends_question_id` int(11) NOT NULL,
  `depends_answer_val` int(11) NOT NULL,
  PRIMARY KEY (`question_id`,`depends_question_id`)
);
INSERT INTO question_depends ( question_id, depends_question_id, depends_answer_val ) VALUES(3, 1, 0);
INSERT INTO question_depends ( question_id, depends_question_id, depends_answer_val ) VALUES(2, 1, 1);
INSERT INTO question_depends ( question_id, depends_question_id, depends_answer_val ) VALUES(3, 1, 1);

CREATE TABLE `answers` (
  `paper_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `answer_val` int(2) NOT NULL,
  PRIMARY KEY (`paper_id`,`question_id`)
); 
INSERT INTO answers ( paper_id, question_id, answer_val ) VALUES(1002, 1, 1);
INSERT INTO answers ( paper_id, question_id, answer_val ) VALUES(1002, 4, 0);
INSERT INTO answers ( paper_id, question_id, answer_val ) VALUES(1004, 1, 0);
INSERT INTO answers ( paper_id, question_id, answer_val ) VALUES(1004, 3, 1);
INSERT INTO answers ( paper_id, question_id, answer_val ) VALUES(1005, 1, 1);

I am trying to find one query that displays all the data for all possible combinations:

  • The identifier of all documents must be displayed at least once
  • This paper_id may or may not have answers and may or may not have a dependency
  • The ultimate goal is to see if the dependencies answered each question, and if so, if the answer val matches the answers of the dependencies val for each paper identifier along with the determination whether paper_id has dependency questions or not, and if any questions are not answered (regardless of whether they have dependencies)
  • I can set up tables / data if necessary

I came close:

select P.paper_id as P_PID,
  A.paper_id as A_PID,
  A.question_id as A_QID,
  A.answer_val as A_VAL,
  QD.question_id as QD_QID,
  QD.depends_question_id AS QD_DQID,
  QD.depends_answer_val AS QD_VAL,
  Q.type_id AS Q_TYPE
from papers P
left join answers A on A.paper_id = P.paper_id
left join question_depends QD on QD.depends_question_id = A.question_id
left join questions Q on Q.question_id = QD.question_id
UNION
select NULL AS P_PID,
  NULL AS A_PID,
  A.question_id as A_QID,
  A.answer_val as A_VAL,
  QD.question_id as QD_QID,
  QD.depends_question_id AS QD_DQID,
  QD.depends_answer_val AS QD_VAL,
  Q.type_id AS Q_TYPE
from question_depends QD
left join answers A on QD.depends_question_id = A.question_id
left join questions Q on Q.question_id = QD.question_id
  where A.question_id IS NULL

... paper_id, paper_id. ! , :

P_PID   A_PID   A_QID   A_VAL   QD_QID  QD_DQID QD_VAL  Q_TYPE
1001                            
1002    1002    1       1       2       1       1       2
1002    1002    1       1       3       1       0       1
1002    1002    4       0       NULL    NULL    NULL    NULL
1003    NULL    NULL    NULL    NULL    NULL    NULL    NULL
1004    1004    1       0       2       1       1       2
1004    1004    1       0       3       1       0       1
1004    1004    3       1       NULL    NULL    NULL    NULL
1005    1005    1       1       2       1       1       2
1005    1005    1       1       3       1       0       1
1006    NULL    NULL    NULL    NULL    NULL    NULL    NULL

( ):

P_PID   A_PID   A_QID   A_VAL   QD_QID  QD_DQID QD_VAL  Q_TYPE
1001    NULL    NULL    NULL    2       1       1       2
1001    NULL    NULL    NULL    3       1       0       1
1002    1002    1       1       2       1       1       2 
1002    NULL    NULL    NULL    3       1       0       1
1002    1002    4       0       NULL    NULL    NULL    3
1003    NULL    NULL    NULL    2       1       1       2
1003    NULL    NULL    NULL    3       1       0       1
1004    1004    1       0       2       1       1       2 
1004    NULL    NULL    NULL    3       1       0       1
1004    1004    3       1       NULL    NULL    NULL    1
1005    1005    1       1       2       1       1       2
1005    NULL    NULL    NULL    3       1       0       1
1006    NULL    NULL    NULL    2       1       1       2
1006    NULL    NULL    NULL    3       1       0       1
+4
1

" ". .

 SELECT /*Answers and no depends*/ p.paper_id AS p_pid,
  a.paper_id AS a_pid,
  a.question_id AS a_qid,
  a.answer_val AS a_val,
  qd.question_id AS qd_qid,
  qd.depends_question_id AS qd_dqid,
  qd.depends_answer_val AS qd_val,
  q.type_id AS q_type
 FROM papers p
 JOIN answers a 
    ON a.paper_id = p.paper_id
 LEFT OUTER JOIN question_depends qd 
    ON  a.question_id = qd.depends_question_id 
        AND 
        a.answer_val = qd.depends_answer_val
 LEFT OUTER JOIN questions q 
    ON q.question_id = a.question_id
 WHERE qd.question_id IS NULL
UNION
SELECT /*Answers and depends*/ p.paper_id AS p_pid,
  a.paper_id AS a_pid,
  a.question_id AS a_qid,
  a.answer_val AS a_val,
  qd.question_id AS qd_qid,
  qd.depends_question_id AS qd_dqid,
  qd.depends_answer_val AS qd_val,
  q.type_id AS q_type
 FROM papers p
 JOIN answers a 
    ON a.paper_id = p.paper_id
 LEFT OUTER JOIN question_depends qd 
    ON  a.question_id = qd.depends_question_id 
        AND 
        a.answer_val = qd.depends_answer_val
 LEFT OUTER JOIN questions q 
    ON q.question_id = qd.question_id
 WHERE qd.question_id IS NOT NULL
UNION 
SELECT /*Missing answer*/ p.paper_id AS p_pid,
  a.paper_id AS a_pid,
  a.question_id AS a_qid,
  a.answer_val AS a_val,
  qd.question_id AS qd_qid,
  qd.depends_question_id AS qd_dqid,
  qd.depends_answer_val AS qd_val,
  q.type_id AS q_type
 FROM papers p
 CROSS JOIN question_depends qd
 JOIN questions q 
    ON q.question_id = qd.question_id
 LEFT OUTER JOIN answers a 
    ON a.paper_id = p.paper_id
        AND
        a.question_id = qd.depends_question_id
        AND
        a.answer_val = qd.depends_answer_val
 WHERE a.question_id IS NULL 
 ORDER BY 1, 7 DESC;

SQL Fiddle http://sqlfiddle.com/#!9/fbd3a9/3

0

Source: https://habr.com/ru/post/1684691/

More articles:


All Articles