DROP PROCEDURE IF EXISTS `sp_content_results_b`;;
CREATE DEFINER=`clofquist`@`localhost` PROCEDURE `sp_content_results_b`(iqmnum  int)
BEGIN

select contentID, section_name, ordernum, content_text,fileId, orientation,`show`, googleId, group_concat(slide_number) slidenum

from (
	SELECT distinct ioc.id contentID, ios.section_name, ios.ordernum, ioc.content_text, ioc.fileId, ios.orientation, ios.`show`, googleId,
			cl.slide_number,
	group_concat(Uanswers.q_value_id) userAnswers,group_concat(cl.question_value_id) requiredAnswers -- group_concat(concat(cl.question_value_id,cl.group_type))

    -- select *
    from


				   (select * from iqm_output_content where statusId=1) ioc
                  join (select * from iqm_output_section where statusId=1 and typeId=30) ios on ioc.section_id=ios.id




                  left join (select cl1.content_id,question_value_id,content_logic_id,'' group_type,cl1.slide_number

                            from iqm_question_values_content iqvc
							join iqm_content_logic cl1 on cl1.id=iqvc.content_logic_id
                            where cl1.parent_id is null

							union select cl2.content_id,question_value_id,content_logic_id,cl2.group_type,cl2.slide_number
                            from iqm_question_values_content iqvc
							join iqm_content_logic cl2 on cl2.id=iqvc.content_logic_id
                            join iqm_content_logic cl1 on cl1.id=cl2.parent_id
                            where cl1.parent_id is null

                            union select cl3.content_id,question_value_id,content_logic_id,cl3.group_type,cl3.slide_number
                            from iqm_question_values_content iqvc
							join iqm_content_logic cl3 on cl3.id=iqvc.content_logic_id
                            join iqm_content_logic cl2 on cl2.id=cl3.parent_id
                            join iqm_content_logic cl1 on cl1.id=cl2.parent_id

                            where cl1.parent_id is null
                            order by content_id,slide_number,group_type,question_value_id)  cl  on cl.content_id=ioc.id                      -- =iqvc.question_value_id -- cl.slide_number=Uanswers.slide_number
                  --

                  left join (select distinct iqv.id q_value_id -- iqvc.slide_number, iqvc.question_value_id

                  -- select *, locate(iqv.value, A.answer )
                  from iqm_questions Q
                  join (SELECT iqmid,questionid, answer,replace(concat('''',answer,''''),', ',''',''') answers
                  FROM iqm_answers WHERE iqm_answers.iqmid = iqmnum) A on A.questionid=Q.id

                  join iqm_question_values iqv on  A.questionid=iqv.question_id -- and iqv.value=A.answer
                  -- join iqm_question_values_content iqvc on iqv.id=iqvc.question_value_id
                  -- WHERE A.iqmid = 1444
                    where locate(iqv.value, A.answer )>0) Uanswers on cl.question_value_id=Uanswers.q_value_id
                       -- gonogo='go' and


                 group by ioc.id, ios.section_name, ios.ordernum, ioc.content_text, ioc.fileId, ios.orientation, ios.`show`, googleId,cl.slide_number

                 having  userAnswers=requiredAnswers

    order by googleId,slide_number) MatchedSlides

    group by contentID, section_name, ordernum, content_text,fileId, orientation,`show`, googleId
    order by ordernum;

END;;