-underandsubcategoriesinmysqltables(coldfusion,mysql)-underandsubcategoriesinmysqltables(coldfusion,mysql)

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 🙂

But there is a problem. In the product_cat table, there are two types: “under” and “sub”. So within each “under” category, there are no products, but there are “sub” categories. When I try to list a category with only products defined with is_extranet=1, the “deficient” category is not listed. But I also want the “under” category listed. In other words, if the content in the “sub” category is a product defined with is_extranet = 1, then the “under” category is displayed, and then its subcategories are displayed with those products. I hope I’m clear 🙂

Plus, the hierarchy for “under” category looks like this: 100 and for sub: 100.001

Also, the hierarchy of the “under” category looks like this: 100 and sub: 100.001

1 solution

#1

1

On the basis that PC.HIERARCHY is unique, this works for me:

Based on PC.HIERARCHY’s unique foundation, this worked for me:

SELECT PC.PRODUCT_CAT,PC.PRODUCT_CATID,PC.HIERARCHY
   FROM PRODUCT_CAT PC
   WHERE LEFT(PC.HIERARCHY,3) IN

   (SELECT DISTINCT LEFT(PC.HIERARCHY,3)
     FROM PRODUCT_CAT PC
     INNER 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;
 

Alternatively:

SELECT PC.PRODUCT_CAT,PC.PRODUCT_CATID,PC.HIERARCHY
   FROM PRODUCT_CAT PC
   INNER JOIN
   (SELECT LEFT(PC.HIERARCHY,3) AS UPPER
     FROM PRODUCT_CAT PC
     INNER JOIN PRODUCT P ON P.PRODUCT_CATID=PC.PRODUCT_CATID
     WHERE P.IS_EXTRANET=1) AS H
     ON LEFT(PC.HIERARCHY,3)=H.UPPER
 GROUP BY PC.PRODUCT_CATID,PC.PRODUCT_CAT,PC.HIERARCHY
 ORDER BY PC.HIERARCHY,PC.PRODUCT_CAT;
 

I’m not sure which will give better performance. The fiddle is here

I’m not sure which one provides better performance. The violin is here