0
As I could align the values in this SQL query per column, are 2 possible questions for each question in a list of several users who answered the first and second question:
- question one? answer a
- question two? answer two
This is the SQL extraction of these answers today:
SELECT
evuser.usr_id as user_id,
(case when (quest.evl_id = 4 and quest.evq_id = 9) then evq_question_name end) as pergunta1,
(case when (quest.evl_id = 4 and quest.evq_id = 10) then evq_question_name end) as pergunta2,
(case when eua.evq_id = 9 then eua_answer end) as resposta1,
(case when eua.evq_id = 10 then eua_answer end) as resposta2
from evaluation_user_answers eua
left join evaluation_user_applied evuser
on eua.eua_id = evuser.eua_id
left join evaluation_questions quest
on quest.evq_id = eua.evq_id
where
eua.evq_id in (9, 10) and quest.evl_id=4;
Tables:
CREATE TABLE `evaluation_questions` (
`evq_id` int(11) NOT NULL AUTO_INCREMENT,
`evl_id` int(3) NOT NULL,
`evq_question_name` varchar(100) DEFAULT NULL,
`evl_type` int(1) DEFAULT NULL,
`evl_order` int(1) DEFAULT NULL,
`evl_max_pontuation` int(2) DEFAULT NULL,
`evl_required` bit(1) DEFAULT NULL,
`evl_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`evl_updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`evq_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
CREATE TABLE `evaluation_user_applied` (
`eua_id` int(11) NOT NULL AUTO_INCREMENT,
`evp_id` int(11) NOT NULL,
`usr_id` int(11) NOT NULL,
`eua_status` tinyint(1) DEFAULT NULL,
`eua_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`eua_updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`eua_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
CREATE TABLE `evaluation_user_answers` (
`eua_id` int(11) NOT NULL,
`evq_id` int(11) NOT NULL,
`eua_answer` varchar(300) DEFAULT NULL,
`eua_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`eua_updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
See that if I catch group 9 he brings me like this:
If I put the group 10, like this:
But if I put the two together, you’re coming up empty, I’d like to get everything lined up in the columns:
See if in this example created would be a valid answer to your question, if I understood correctly, could solve without using the
CASE WHEN
. See example http://sqlfiddle.com/#! 9/948113/11.– Clarck Maciel
Using
CASE WHEN
, could look like this http://sqlfiddle.com/#! 9/948113/14?– Clarck Maciel