cfloop and query of queries

Question

I have a scenario like below on a page. I have a query being returned from a cfc all_data which has columns, section, state, data. Now the page design looks something like below.

section1 section2 section3 section4 -> select one section to select the state in it

Lets say section1 is selected -> State1 state2 state3 associated with that section need to be displayed -> select one state to see the data related to it.

Say State3 is selected -> related State3 data is shown.

So basically I need 3 cfloops which will accomplish the above. I am doing `

<cfquery name="section" dbtype="query">
 select distinct section from all_data 
</cfquery>`

for first loop, I loop over 'section' query to display all the sections.

<cfloop query ="section">
    <cfquery name="state" dbtype="query">
    select distinct state from all_data where section = section.section
    </cfquery>
</cfloop>

for state display I loop like above. For loop 3 that is the data display I have tried multiple things but nothing seems to work the right way. Is this the right approach. Any insights are appreciated.

Answer 1

I think you can use the group attribute as below

<cfset myQuery = QueryNew("Section, State, Data", "VarChar, VarChar, VarChar")> 

<cfset newRow = QueryAddRow(MyQuery, 5)> 

<!--- Set the values of the cells in the query ---> 
<cfset temp = QuerySetCell(myQuery, "Section", "Section 1", 1)> 
<cfset temp = QuerySetCell(myQuery, "State", "State 1", 1)> 
<cfset temp = QuerySetCell(myQuery, "Data", "Data 1", 1)> 
<cfset temp = QuerySetCell(myQuery, "Section", "Section 1", 2)> 
<cfset temp = QuerySetCell(myQuery, "State", "State 2", 2)> 
<cfset temp = QuerySetCell(myQuery, "Data", "Data 2", 2)> 
<cfset temp = QuerySetCell(myQuery, "Section", "Section 1", 3)> 
<cfset temp = QuerySetCell(myQuery, "State", "State 2", 3)> 
<cfset temp = QuerySetCell(myQuery, "Data", "Data 3", 3)> 
<cfset temp = QuerySetCell(myQuery, "Section", "Section 2", 4)> 
<cfset temp = QuerySetCell(myQuery, "State", "State 2", 4)> 
<cfset temp = QuerySetCell(myQuery, "Data", "Data 2", 4)> 
<cfset temp = QuerySetCell(myQuery, "Section", "Section 2", 5)> 
<cfset temp = QuerySetCell(myQuery, "State", "State 2", 5)> 
<cfset temp = QuerySetCell(myQuery, "Data", "Data 3", 5)> 

 <cfoutput query ="myQuery" group="Section">
    </br>#Section# <!--- You will get distinct Sections here --->
    <cfoutput group="Section">
            </br>#State#,
            <cfoutput>#Data#,</cfoutput>
    </cfoutput>
 </cfoutput>

Answer 2

do you mean something like this?

    <cfloop query ="section">
        <cfquery name="state" dbtype="query">
          select distinct state from all_data where section = section.section;
        </cfquery>
        <cfloop query ="state">
            <cfquery name="getdata" dbtype="query">
              select * from all_data where section = section.section 
              and state = state.state;
            </cfquery>
            <cfdump var=#getdata#>
        </cfloop>
    </cfloop>