survey Report mysql Query

[kalyana1315]

hi alll

survey Report sql

select t.tn as "Ticket number",t.customer_id as company,
concat(cu.first_name," ",cu.last_name) as "Customer name",
CONCAT( u2.first_name, " ", u2.last_name ) owner,q.name queue,
sv.create_time as "vote time",
sq.question , sa.answer
from ticket t
inner join survey_request s on t.id = s.ticket_id
inner join survey_vote sv on sv.request_id = s.id
left join survey_question sq on sq.id = sv.question_id
left join survey_answer sa on sa.id = sv.vote_value
inner join customer_user cu on cu.login = t.customer_id
inner JOIN users u2 ON u2.id = t.user_id
inner JOIN queue q ON t.queue_id = q.id
where t.id in ( select distinct ticket_id from survey_request )

[kalyana1315]

if more then one survey than use status is master

added two line

select t.tn as "Ticket number",t.customer_id as company,
concat(cu.first_name," ",cu.last_name) as "Customer name",
CONCAT( u2.first_name, " ", u2.last_name ) owner,q.name queue,
sv.create_time as "vote time",
sq.question , sa.answer
from ticket t
inner join survey_request s on t.id = s.ticket_id
inner join survey_vote sv on sv.request_id = s.id
left join survey_question sq on sq.id = sv.question_id
left join survey_answer sa on sa.id = sv.vote_value
inner join customer_user cu on cu.login = t.customer_id
inner JOIN users u2 ON u2.id = t.user_id
inner JOIN queue q ON t.queue_id = q.id
inner join survey sr on sr.id = sq.survey_id
where t.id in ( select distinct ticket_id from survey_request )
and sr.status like 'master'

[xantippe]

Latest version, replaced customer_id with customer_user_id to get proper results and corrected answer column...

select t.tn as "Ticket number",t.customer_id as company,
concat(cu.first_name," ",cu.last_name) as "Customer name",
CONCAT( u2.first_name, " ", u2.last_name ) owner,q.name queue,
sv.create_time as "vote time",
sq.question ,
CONCAT_WS(' - ',REPLACE(sv.vote_value,'$html/text$ ','') ,sa.answer) as answer
from ticket t
inner join survey_request s on t.id = s.ticket_id
inner join survey_vote sv on sv.request_id = s.id
left join survey_question sq on sq.id = sv.question_id
left join survey_answer sa on sa.id = sv.vote_value
inner join customer_user cu on cu.login = t.customer_user_id
inner JOIN users u2 ON u2.id = t.user_id
inner JOIN queue q ON t.queue_id = q.id
inner join survey sr on sr.id = sq.survey_id
where t.id in ( select distinct ticket_id from survey_request )
and sr.status like 'master'
ORDER by t.tn, sv.question_id