Ditto on the repost, not sure if my first reply posted... Actually it's much more than that. There are a ton of categories that won't have products, however, if they are a parent of a sub category that has a product, they ultimately have products as well. This is why it requires nested queries about 5 levels deep in coldfusion. Did you happen to scroll down enough to see the plethora of <CFIF statements and nested queries?
I wish it were that simple. :) > Sorry if this is a repost. I am not sure it posted the first time. > > I do not think any looping is required. You should be able to do this > using sub queries within the where clause. > > If you are using cfx_prod_custCat as a lookup table simply look for > any category that is not listed in the custcat_id field: > > UPDATE Categories SET hasproducts = 0 WHERE category IN > (SELECT DISTINCT category FROM Categories WHERE ID NOT IN > (SELECT DISTINCT custcat_id FROM cfx_prod_custCat)) > > You use the same logic for the 2nd part of the UNION clause: > > UPDATE Categories SET hasproducts = 0 WHERE category IN > (SELECT DISTINCT category FROM Categories WHERE category NOT IN > (SELECT DISTINCT category FROM products)) > > > Basically you use the sub query to pull a list of all products that > have a category assigned and the look for categories that are not in > that list. > > Hopefully I am not way off on this and it helps point you in the right > direction. > > Brian Cain ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341567 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
