Drilldown Query of Queries

[_Bryan_]

I'm using Coldfusion 9 standalone.

I'm still somewhat new at this, but I found drilldown code that fits my needs for my app. It uses a Query of Queries which to some extent I understand. My problem comes in when I want to take the drilldown a couple of steps further. The first query lists a products that are associated to related sub-products. By clicking a product it will display a list of associated sub-categories. I would then like to link each sub-product to a detail page, but I'm not sure if my sub-product Link code is correct, and I'm not sure what the detail page query should look like to show that specific sub-product detail. Here's the code I have so far:

Product Drilldown Page:

<cfquery name="GetDetail" datasource="mm_instance" cachedwithin="1">
SELECT products.productID, products.product, instance.instances
FROM Products, Instance, ProductInstance
WHERE products.ProductID=ProductInstance.productID AND ProductInstance.instanceID=instance.instanceID
</cfquery>
<cfquery dbtype="query" name="drilldown">
SELECT DISTINCT productID, product
FROM GetDetail
ORDER BY product
</cfquery>

<cfoutput query="DrillDown">
<table width="100%" id="box">
     <tr>
          <td valign="top" width="510" id="prodname"><a href="Inst_List.cfm?ProductID=#DrillDown.ProductID#">#DrillDown.product#<br></a></td>
     </tr>
</table>
</cfoutput>

Sub-Product (or Instance) Drilldown Page:

<cfquery name="GetDetail" datasource="mm_instance" cachedwithin="1">
SELECT products.productID, products.product, instance.instances
FROM products, Instance, ProductInstance
WHERE products.ProductID=ProductInstance.productID AND ProductInstance.instanceID=instance.instanceID
</cfquery>
<cfquery dbtype="query" name="drilldown">
SELECT instances, productID
FROM GetDetail
WHERE GetDetail.productID = #url.productID#
ORDER BY product
</cfquery>

<cfoutput query="DrillDown">
<table width="100%" id="box">
     <tr>
          <td valign="top" width="510" id="prodname"><a href="Inst_Detail.cfm?productID=#DrillDown.productID#">#DrillDown.instances#</a></td>
     </tr>
</table>
</cfoutput>

Sub-Product Detail Page:

Don't know what this query should look like

Thanks!

[Dan_Bracuk · Accepted solution]

You are running too many queries.  On the first page, just select a distinct set of products and their id's, unless you need that instance value for something else.  On your drilldown page, you don't have to select all the products again.  Just select the one from the url variable.