CODEWITHSUNDEEP
mysqlcoldfusion
Bato Dor
Bato Dor

Reputation: 841

under and sub categories in mysql tables (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:

<cfquery name="get_product_cat" datasource="#dsn1#">
    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
</cfquery>

and the output:

<select name="product_catid">
    <option value="">All Categories</option>
    <cfoutput query="get_product_cat">
          <option value="#product_catid#" <cfif isdefined("attributes.product_catid") and len(attributes.product_catid) and (attributes.product_catid eq product_catid)>selected</cfif>><cfloop from="2" to="#listlen(hierarchy,'.')#" index="pc">&nbsp;&nbsp;</cfloop>#product_cat#</option>
    </cfoutput>
</select>

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 :)

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

Upvotes: 1

Views: 195

Answers (1)

grahamj42
grahamj42

Reputation: 2762

On the basis that PC.HIERARCHY is unique, this works 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

Upvotes: 1

Related Questions