conditional queries

I have a db that contains data gathered through multiple online questionnaires.

So one of the things that I have todo with this data is list all the questions with a negative response on a certain questionnaire. However there are 3 questions which require the user to fill out additional questionnaire if the answer yes.

in extracting the data, this is the logic behind what I would like to do:

query response table for distinct item ids
query response tbl, case tbl, question tbl and return a record set for each distinct id

it the response field has a value of 'No' or the question id is 115, 116, or 118 and the response for those questions is 'Yes'
print the case number, question text, response, and question comment if not blank else print 'No comment'
if the question id is 115, 116, or 118 and the response for those questions is 'Yes' ALSO query the response tble and question tbl for responses to questionnaire 96.
indent and print the responses to questionnaire 96

get next question response for initial query


this logic works with one exception; the secondary query [in blue] never fires even if the response is yes.


I believe it may have some thing to do with the nesting of the out put tags but I'm sure because "NO ERRORS" are being thrown.

Actual code looks some thing like this:

<cfloop query="getPE_Caseitems"><!--- actual queries is in rpt_case_review --->

 

            <cfquery name="getPE_response" datasource="#application.db#"><!--- returns reponses based on item ids --->

            Select  r.*, c.case_id, c.case_title, c.case_file_number, q.question_text

            from (response_tbl r INNER JOIN case_tbl c ON r.inspection_id = c.inspection_id and r.case_id = c.case_id) INNER JOIN question_tbl q ON r.question_id = q.question_id

            where r.inspection_id = #session.inspection# and  r.questionaire_id = 21 and  r.case_id = #getPE_Caseitems.item_id#

            </cfquery>

            <ul>

            <cfoutput query="getPE_response">

           

                      

            <cfif getPE_response.response IS 'No' or (getPE_response.question_id is 1115 and getPE_response.response IS 'Yes') or (getPE_response.question_id is 1116 and getPE_response.response IS 'Yes') or (getPE_response.question_id is 1118 and getPE_response.response IS 'Yes')>

 

                        <li><strong>CASE - #case_file_number#</strong><br>

                        #question_text#<br>#response# - <cfif question_comment is ''>No comments <cfelse>#question_comment#</cfif>

                        </cfif>

                        </cfoutput>

                                    <cfif getPE_response.question_id is 1115 and response IS 'Yes'>

                                    

                                   [COLOR="RoyalBlue"] <cfquery name="getCM_response" datasource="#application.db#">

                                                Select  r.*, c.case_id, c.case_title, c.case_file_number, q.question_text

                                                from (response_tbl r INNER JOIN case_tbl c ON r.inspection_id = c.inspection_id and r.case_id = c.case_id) INNER JOIN question_tbl q ON r.question_id = q.question_id

                                                where r.inspection_id = #session.inspection# and  r.questionaire_id = 96 and  r.case_id = #getPE_Caseitems.item_id#

                                    </cfquery>
[/COLOR]
                                    

                                    <ul>

            <cfoutput query="getCM_response">

                                    <li><strong>CASE - #case_file_number#</strong><br>

                        #question_text#<br>#response# - <cfif question_comment is ''>No comments <cfelse>#question_comment#</cfif></li>

                                   

                                    </CFOUTPUT>

                                   

                                   

                                   

                                   

                                    <cfelseif getPE_response.question_id is 1116 and response IS 'Yes'>

                                   
                                    <cfquery name="getUC_response" datasource="#application.db#">

                                                Select  r.*, c.case_id, c.case_title, c.case_file_number, q.question_text

                                                from (response_tbl r INNER JOIN case_tbl c ON r.inspection_id = c.inspection_id and r.case_id = c.case_id) INNER JOIN question_tbl q ON r.question_id = q.question_id

                                                where r.inspection_id = #session.inspection# and  r.questionaire_id = 97 and  r.case_id = #getPE_Caseitems.item_id#

                                    </cfquery>

                                    <ul>

            <cfoutput query="getUC_response">

                                    <li><strong>CASE - #case_file_number#</strong><br>

                        #question_text#<br>#response# - <cfif question_comment is ''>No comments <cfelse>#question_comment#</cfif></li>

                                   

                                    </CFOUTPUT>

                                     

                                     

                                     <cfelseif getPE_response.question_id is 1118 and response IS 'Yes'>

                                     

                                     

                             

                                    <cfquery name="getCICS_response" datasource="#application.db#">

                                                Select  r.*, c.case_id, c.case_title, c.case_file_number, q.question_text

                                                from (response_tbl r INNER JOIN case_tbl c ON r.inspection_id = c.inspection_id and r.case_id = c.case_id) INNER JOIN question_tbl q ON r.question_id = q.question_id

                                                where r.inspection_id = #session.inspection# and  r.questionaire_id = 73 and  r.case_id = #getPE_Caseitems.item_id#

                                    </cfquery>

                                    <ul>

            <cfoutput query="getCICS_response">

                                    <li><strong>CASE - #case_file_number#</strong><br>

                        #question_text#<br>#response# - <cfif question_comment is ''>No comments <cfelse>#question_comment#</cfif></li>

                                    </CFOUTPUT>

                       
                                     </cfif>

                         </li>
            </ul>

   </cfloop>

Code (markup):

thanks in advance

 

disregard previous post, I figured it out.