Thanks for responding Art!
Let me verify what I see in your SQL. All your categories live in the same
table, pcategory. You give that table 3 different 'aliases' though?
Also it seems as though you know ahead of time that all your products are 3
categories deep?
This brings me to the conclusion that this SQL is used to return all the
products that are 3 categories deep?
Am I on track here Art? :)
jeff
-----Original Message-----
From: Art Broussard [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 08, 2001 11:38 AM
To: CF-Talk
Subject: Re: Maybe OT: Infinite categories/subcategories
You are doing the same with your database schema that I am. The one thing
that will make it dead dog slow is the <loop> query
</loop> scenario.
You will want to do a join in your query and then do an output using a group
by. Here is a example of what I'm doing.
Art
<cfquery name="category" datasource="#application.datasource#">
SELECT
t1.cat_id AS cat_id, t1.cat_name AS cat_name, t1.cat_parent_id AS
cat_parent_id,
t2.cat_id AS sub_cat_id, t2.cat_name AS sub_cat_name, t2.cat_parent_id AS
cat_parent_id,
t3.cat_id AS sub_sub_cat_id, t3.cat_name AS sub_sub_cat_name,
t3.cat_parent_id AS sub_cat_parent_id,
p.product_id, p.producttitle, p.cat_parent_id, p.company_id, p.release_date
FROM
pcategory t1, pcategory t2, pcategory t3, product p
WHERE
t2.cat_parent_id = t1.cat_id
AND
t3.cat_parent_id = t2.cat_id
AND
p.cat_parent_id = t3.cat_id
ORDER BY
t1.cat_name, t2.cat_name, t3.cat_name, p.producttitle
</cfquery>
<!--- DISPLAY CAT NAME --->
<CFOUTPUT QUERY="category" GROUP="cat_name">
<B>#cat_name#</B><br>
<!--- DISPLAY SUBCAT NAME --->
<CFOUTPUT GROUP="sub_cat_name">
<B>#sub_cat_name#</B><br>
<!--- DISPLAY SUBSUBCAT NAME --->
<CFOUTPUT GROUP="sub_sub_cat_name">
<B>#sub_sub_cat_name#</B><br>
<!--- DISPLAY PRODUCT LINK --->
<CFOUTPUT GROUP="producttitle">
<b>#producttitle#</b><br>
</CFOUTPUT>
</CFOUTPUT>
</CFOUTPUT>
</CFOUTPUT>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
