1

i am in difficulties in doing following table (mysql). i have stdent-id,questions,answers and questions-id of a table. below given, is there any way to output table something below output table?

My MySql query following

SELECT a.`person_id` AS STUDENT_ID,q.`questions`,a.`answer` as ANS,q.`question_id`
FROM
questions q
left join answer a on a.`question_id`=q.`question_id`
left join table_info t on q.`question_id`=t.`question_id`

after this query table look like this

 student_id   Question    Answer    Question_Id Ans_id
------------------------------------------------------------
    1           Name        Jonathan     1       1
    1           Age         12           2       2
    1           Sex         Male         3       3
    1           Area        LA           4       4
    2           Name        Mary         1       5
    2           Age         11           2       6
    2           Sex         Female       3       7
    2           Area        NY           4       8
    3           Name        David        1       9
    3           Age         12           2       10
    3           Sex         Male         3       11
    3           Area        NY           4       12

I need output like following

Student_Id   Name       Age     Sex       Area
-----------------------------------------------
1            Jonathan   12      Male      La
2            Mary       11      Female    NY
3            David      12      Male      NY

Questions table:

CREATE TABLE `questions` (
  `question_id` int(11) NOT NULL AUTO_INCREMENT,
  `questions` varchar(250) DEFAULT NULL,
  `questions_cat` int(11) DEFAULT NULL COMMENT 'which category falls this questions. when load questions, then we can load questions by category',
  `question_order` int(11) DEFAULT NULL COMMENT 'sequence/order of the Q',
  `questions_status` varchar(45) DEFAULT NULL,
  `questions_type_id` int(11) NOT NULL,
  `text_question_top` varchar(200) DEFAULT NULL,
  `text_question_bottom` varchar(200) DEFAULT NULL,
  `input_class` varchar(100) DEFAULT NULL,
  `input_id` varchar(50) DEFAULT NULL COMMENT 'eg id="idName"',
  `input_placeholder` varchar(50) DEFAULT NULL COMMENT 'PlaceHolder',
  `input_onchange` varchar(200) DEFAULT NULL COMMENT 'Onchange, like JS',
  `input_other` varchar(200) DEFAULT NULL COMMENT 'text only, eg ng-click="callFunction"',
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `update_by` int(11) DEFAULT NULL,
  `view_text` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`question_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

Answer table:

CREATE TABLE `answer` (
  `answer_id` int(11) NOT NULL AUTO_INCREMENT,
  `answer` varchar(300) DEFAULT NULL,
  `question_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `answer_other` varchar(100) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `update_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`answer_id`,`question_id`,`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8

table_info table:

CREATE TABLE `table_info` (
  `tableInfo_id` int(11) NOT NULL AUTO_INCREMENT,
  `table_link` varchar(200) NOT NULL,
  `function` varchar(100) DEFAULT NULL,
  `question_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`tableInfo_id`),
  KEY `fk_tableInfo_Questions` (`question_id`),
  CONSTRAINT `fk_tableInfo_Questions` FOREIGN KEY (`question_id`) REFERENCES `questions` (`question_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
2
  • Please, add your table data as text, so that it can be copied easily by people trying to give you a response.
    – joanolo
    Commented Jan 19, 2017 at 7:50
  • This does not help your problem, but the column names "question" and "answer" are very wrong. Question should probably be renamed to "attribute" and Answer to "value".
    – til_b
    Commented Jan 19, 2017 at 9:44

1 Answer 1

1
SELECT Student_Id,
       ( SELECT Answer FROM t
             WHERE student_id = x.Student_Id
               AND Question = 'Name' ) AS Name,
       ( SELECT Answer FROM t
             WHERE student_id = x.Student_Id
               AND Question = 'Age' ) AS Age,
       ( SELECT Answer FROM t
             WHERE student_id = x.Student_Id
               AND Question = 'Sex' ) AS Sex,
       ( SELECT Answer FROM t
             WHERE student_id = x.Student_Id
               AND Question = 'Area' ) AS Area
    FROM ( SELECT DISTINCT Student_Id FROM t ) AS u
    ORDER BY Student_Id;

Performance would benefit from

INDEX(Student_Id, Question, Answer)
3
  • i have added my mysql code, would you please help on it ? Commented Jan 19, 2017 at 6:33
  • We need to see SHOW CREATE TABLE for the tables in use ! My answer assumed (wrongly) that your first described a single table with the data.
    – Rick James
    Commented Jan 19, 2017 at 22:44
  • table information given, please try if you can solve this problem, and if you can do this dynamically then it would be great because my questions get change or add or del any time. Commented Jan 20, 2017 at 11:43

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.