[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Query and Output

Experts-

I have these two queries that I am outputting.  

 <cfquery name="getTransInfo" datasource="#REQUEST.datasource#">
        SELECT sr.StudentID, sr.ItemID, COUNT(*) AS TotalTNum, s.fName, s.lName, s.classID
        FROM   studentresponse sr
        INNER JOIN answers a ON a.ItemID = sr.ItemID AND a.answer = sr.Response
        INNER JOIN itemtoItemset i on i.item = a.itemID AND i.itemsetID  = sr.itemsetID
        INNER JOIN students s on s.studentID = sr.studentID  
        WHERE sr.itemsetID = <cfoutput>#thisTestID#</cfoutput>
        AND sr.itemID IN (919,932)
        AND a.answerID ='M'
        AND classID = <cfoutput>#thisID#</cfoutput>
        GROUP BY sr.StudentID
        HAVING COUNT(*) >=2
</cfquery>
</cfif>
          
<cfquery name="getAllInfo" datasource="#REQUEST.datasource#">
      SELECT sr.StudentID, sr.ItemID, COUNT(*) AS TotalNum, s.fName, s.lName, s.classID
      FROM   studentresponse sr
    INNER JOIN answers a ON a.ItemID = sr.ItemID AND a.answer = sr.Response
    INNER JOIN itemtoItemset i on i.item = a.itemID AND i.itemsetID  = sr.itemsetID
    INNER JOIN students s on s.studentID = sr.studentID  
    WHERE sr.itemsetID = <cfoutput>#thisTestID#</cfoutput>
   AND a.answerID ='M'
    AND classID = <cfoutput>#thisID#</cfoutput>
    GROUP BY sr.StudentID
    HAVING COUNT(*) <cfoutput>#thisCount#</cfoutput>
</cfquery>

If a student is in the getTransInfo query, I want to subtract 1 from the students count in the getAllIinfo query.  

I was using this code below, but I am not getting what I expect.  I think what's happening is it finds the first studentID, and runs through the code below and comes up null for those that are actually in the first query.  Can someone tell me what I am doing wrong in this output below?  Any help would be appreciated


<cfoutput query="getAllInfo">
      
    <ul style="list-style-type:square">
   
    <cfif getTransInfo.studentID EQ StudentID>
            <cfset thisStudentCount = #totalNum# - 1>
    <cfelse>
            <cfset thisStudentCount = #totalNum#>
   
            <cfif thisStudentCount GTE 5>
        <li>#fName# #lName#: chose the misconception response for <strong>#totalNum# questions</strong></li>
        </cfif>
    </cfif>
   
   
    </ul>
    </cfoutput>
0
nmarano
Asked:
nmarano
  • 3
  • 2
1 Solution
 
gdemariaCommented:
I think the problem is that you aren't looping through both queries.   Each each student of getAllInfo, you need to check EVERY record of getTransInfo and you're not looping getTransinfo at all.

An easier way to do it is to make a list of student IDs from the getTransInfo query and then each time you loop through getAllInfo, just check the list to see if the student ID is on the list...  then you only have to loop one table while checking the list of IDs from the other...

  <cfset allTransStudentIDs = valueList(getTransInfo.studentID)>
  <cfoutput query="getAllInfo">
    <cfif listFind(allTransStudentIDs,getAllInfo.studentID)>
       <cfset thisStudentCount = getAllInfo.totalNum - 1>
    <cfelse>
       <cfset thisStudentCount = getAllInfo.totalNum>
    </cfif>


0
 
nmaranoAuthor Commented:
Thanks Maria-

I'll try it out
0
 
nmaranoAuthor Commented:
Hey Maria-

Tried it, but it gave me the same result set.  So now I tried this, but am still getting the same result set.  There are 4 students in allTransStudentIDs list

I tried putting a loop in and looping over the list, but that didn't work.  any suggestions?

<cfset allTransStudentIDs = valueList(getTransInfo.studentID)>
      <cfoutput query="getAllInfo">
   
            <cfif listFind(allTransStudentIDs,getAllInfo.studentID)>
       
            <cfset thisStudentCount = getAllInfo.totalNum - 1>
        <cfelse>
            <cfset thisStudentCount = getAllInfo.totalNum>
       
            <cfif thisStudentCount GTE 5>
            <li>#fName# #lName#: chose the misconception response for <strong>#totalNum# questions</strong></li>
            </cfif>
        </cfif>
   
   </cfoutput>
0
 
gdemariaCommented:

By "that didn't work"  do you mean the end result wasn't want you were after?  Or was there an error?   Do you know which part of the code had a problem?

Let's add some  output statements to get some visibility into what's happening and see where things are going wrong...

<cfset allTransStudentIDs = valueList(getTransInfo.studentID)>

<cfoutput>Trans Student IDs:  #alltransStudentIDs#.   Is this list correct?</cfoutput>

      <cfoutput query="getAllInfo">
    
            <cfif listFind(allTransStudentIDs,getAllInfo.studentID)>
                   Student #getAllInfo.studentID# was on the list, his count was #getAllInfo.totalNum# and is now #getAllInfo.totalNum - 1#<br>
                  <cfset thisStudentCount = getAllInfo.totalNum - 1>
        <cfelse>
                  <cfset thisStudentCount = getAllInfo.totalNum>
                   Student #getAllInfo.studentID# IS NOT on the list, his count is #getAllInfo.totalNum# <br>
        </cfif>

         -- i moved this outside the </cfif> not sure if that is correct or not...
         <cfif thisStudentCount GTE 5>
                       <li>#fName# #lName#: chose the misconception response for <strong>#totalNum# questions</strong></li>
            </cfif>

    
   </cfoutput

Open in new window

0
 
nmaranoAuthor Commented:
Thanks for the help!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now