barney, on my homepage i wish to display all top level categories eg. Summer,
Winter, Sale Items
i then wish to click on Summer and bring up only the child categories of summer
(just one level down).
i have tried the following query (taken from a mysql article on hierarchical
data)...
<cfquery name="getCats" datasource="#Request.App.dsn#"
password="#Request.App.DBpassword#" username="#Request.App.DBusername#">
SELECT child.cat_id, child.cat_title, (COUNT(parent.cat_id) -
(sub_tree.depth + 1)) AS depth
FROM categories AS child, categories AS parent, categories AS
sub_parent,
(
SELECT child.cat_id, (COUNT(parent.cat_id) - 1) AS depth
FROM categories AS child, categories AS parent
WHERE child.cat_lft BETWEEN parent.cat_lft AND parent.cat_rgt
AND child.cat_id = #URL.cat_id#
GROUP BY child.cat_id
) AS sub_tree
WHERE child.cat_lft BETWEEN parent.cat_lft AND parent.cat_rgt
AND child.cat_lft BETWEEN sub_parent.cat_lft AND sub_parent.cat_rgt
AND sub_parent.cat_id = sub_tree.cat_id
GROUP BY child.cat_id, child.cat_title, child.cat_lft, sub_tree.depth
HAVING depth <= 1
ORDER BY child.cat_lft
</cfquery>
this still brings up the 3rd level categories as well?? not sure what i have
done wrong, or maybe this query is not sutable for SQL server?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231059
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
