Da's gerief.

<CFQUERY name="qProdList" datasource="#request.app.mainDSN#">

  select PRODTYPE_DETAILXSL, PRODTYPE_CODE, prodType_ID from tblProdList

 INNER JOIN tblProdTypes ON prodList_prodType_ID = ProdType_ID where ProdList_ID = '#arguments.ID#'

</CFQUERY> 

<CFQUERY name="qProdListRules" datasource="#request.app.mainDSN#">

  SELECT *

  FROM   tblProdListRules INNER JOIN tblProdFields 
            ON tblProdListRules.prodListRule_prodField_ID=tblProdFields.prodField_ID

  WHERE  ProdListRule_ProdList_ID='{#arguments.ID#}'

</CFQUERY>

<CFQUERY name="qProducts" datasource="#request.app.mainDSN#">

    select tblProducts.* from tblProducts 

    <CFLOOP query="qProdListRules">

      inner join tblProdData#ProdField_FT# tbl#qProdListRules.currentrow#

      on tbl#qProdListRules.currentrow#.proddata_Field_id = '{#ProdField_id#}'

      and tbl#qProdListRules.currentrow#.proddata_prod_id = tblProducts.product_ID

      and tbl#qProdListRules.currentrow#.proddata_data

      <CFIF ProdField_ft EQ "NUM">

        #prodListRule_qualifier# #prodListRule_value#

      <CFELSE>

        <CFIF ProdField_inputtype EQ "text">

          #prodListRule_qualifier# '#prodListRule_value#'

        <CFELSE>

        #prodListRule_qualifier# '#prodListRule_value#'

        </CFIF>

      </CFIF>

    </CFLOOP>

  where product_prodType_ID = '{#qProdList.prodType_ID#}'

</CFQUERY>