<CFQUERY NAME="GetProd" DATASOURCE="#DSN#" USERNAME="#UNAME#" PASSWORD="#PWORD#">
        SELECT     *,
                          (SELECT     COUNT(SubCategoryID)
                            FROM          Sort_Subcat AS SSC
                            WHERE      SSC.CategoryID = Category.CategoryID) AS SubCatCount
        FROM         Products INNER JOIN
                      Sort_Products ON Sort_Products.ProductID = Products.ProductID INNER JOIN
                      SubCategory ON Sort_Products.SubCategoryID = SubCategory.SubcategoryID INNER JOIN
                      Sort_SubCat ON SubCategory.SubcategoryID = Sort_SubCat.SubCategoryID INNER JOIN
                      Category ON Sort_SubCat.CategoryID = Category.CategoryID INNER JOIN
                      Sort_SubProducts ON Products.ProductID = Sort_SubProducts.ProductID INNER JOIN
                      SubProducts ON Sort_SubProducts.SubProductID = SubProducts.SubProductID
        WHERE     (Sort_Products.SubCategoryID = '#ShowSubCat#')
        ORDER BY Category.CategoryName, Category.CategoryID, Sort_SubCat.SortOrder, Sort_Products.SortOrder, Sort_SubProducts.SortOrder
        </CFQUERY>