-underandsubcategoriesinmysqltables(coldfusion

I have a question regarding ColdFusion and Mysql. I have two tables: PRODUCT and PRODUCT_CAT. I want to list the categories that contains some of the special products that are marked as: IS_EXTRANET=1. So I wrote this query: I have a question about ColdFusion and Mysql. I have two tables: PRODUCT and PRODUCT_CAT. I want to list categories that contain some special products, which are marked with: IS_EXTRANET = 1. So I wrote this query: SELECT PC.PRODUCT_CAT,PC.PRODUCT_CATID,PC.HIERARCHY FROM PRODUCT_CAT PC LEFT OUTER JOIN PRODUCT P ON P.PRODUCT_CATID=PC.PRODUCT_CATID WHERE P.IS_EXTRANET=1 GROUP BY PC.PRODUCT_CATID,PC.PRODUCT_CAT,PC.HIERARCHY ORDER BY PC.HIERARCHY,PC.PRODUCT_CAT and the output: and output: All Categories <option value="#product_catid#" selected> #product_cat# But there is a problem. Inside the product_cat table, there are 2 types of categories: “under” and “sub”. So inside each “under” category, there are no products, but there are “sub” categories. When I try to list the categories that are only have products with the is_extranet=1 definition, there are no “under” categories listed. But I want the “under” categories listed too. In other words, if inside the “sub” category is a product with the definition is_extranet=1, then show the “under” category, then its subcategories with these products. I hope I was clear …