Sorry that I answer didn't take inheritance into account. Is there a maximum number of levels of subcategories, or will it always be an unknown? I have done looping with tSQL in the past using cursors, and it really is a pain.
You could use a UDF (User Defined Function) and return a table variable. Here is a link for an example of returning a table variable: http://sqlt.tripod.com/recursivity.htm You could probably modify this example to return a bit value if the category or any parent has a product assigned. In theory if you write the function correctly you could write a single update statement that sets the hasproducts column equal to the funtion value passing in the category as a parameter. Something like UPDATE categories set hasprodutcs = fnHasProduct(category) where fnHasProduct id the name of your UDF and accepts the category as a parameter. The real work would be in writing your UDF. You would want you to thoroughly test your UDF, but it could provide you with a powerful and simple execution that even incorporates a little OOP using tSQL. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:341569 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
